Click here to Skip to main content
13,248,927 members (46,936 online)
Rate this:
 
Please Sign up or sign in to 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 11-Aug-17 23:31pm
Updated 12-Aug-17 0:09am

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

You need GROUP BY with a HAVING clause to identify the single entry rows:
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:
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
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web04 | 2.8.171114.1 | Last Updated 12 Aug 2017
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100