Bobski Bobski - 1 year ago 81
SQL Question

SQL Server 2008 - Select query specific item

I have a table with a bunch of columns, and I'm trying to run a query but I'm having a difficult time coming up with it. This is what my table looks like..

clientID Code
17 DAC7
19 DAC8
20 TIM19
21 DAC7
89 TIM19

So here, I have two columns (there are a lot more, but they're actually irrelevant). So the issue that I'm facing is that each client is supposed to have a different code, but there might be cases where 2 different clients have the same code, and that's exactly what I'm trying to find. If you look above Client 17 and 21 have DAC7, and Client 20 and 89 have TIM19.

How would I query this table to show me only the data where there are more than one instance of CODE, something like...

SELECT ClientID, Code
WHERE Count (code) > 1
ORDER by Code

End result I'm hoping for...

17 DAC7
21 DAC7
89 TIM19
20 TIM19

Answer Source

You can also do this with an IN statement:

SELECT clientID, code
    (SELECT code
     FROM T
     GROUP BY code
     HAVING COUNT(*) > 1)

Tested here:!9/fd441a/6

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download