Click here to Skip to main content
15,886,795 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Table :
Id. Type
1. Xxx
1. Bbb
2. Xxx
3. Xxx
4. Yyy
4. Xxx

Output:
Id. Type
2. Xxx
3. Xxx

What I have tried:

Output should display the type containing xxx which having only one entry of id
Posted
Updated 11-Aug-17 23:09pm

1 solution

You need GROUP BY with a HAVING clause to identify the single entry rows:
SQL
SELECT ID FROM MyTable GROUP BY ID HAVING COUNT(ID) = 1
That gives you just the ID values you are interested in:
ID
2
3
You then use a JOIN back to the original table to get the Type and ID together:
SQL
SELECT a.ID, b.Type 
FROM (SELECT ID FROM MyTable GROUP BY ID HAVING COUNT(ID) = 1 ) a
JOIN MyTable b ON a.ID = b.ID
Which gives you the result you want:
ID	Type
2	xxx       
3	xxx
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900