Do you have that one piece of  code that you use every once in a while that you just can’t seem to remember when you need it.  Well, this is one for me.  Most geeks who use SQL Server know what Distinct(*) is used for.  If you have a table that has 20 rows in it but one of the columns has some data it in that is repeated in it, the Distinct will show you just one line for each value.

So, lets say that you have the following values in a table

Phil
Karen
Cheri
Bill
Phil
Phil
Gaylon
Phil
Bill
Phil
Bill

If you did a Select Distinct(*) on that table then you would get back the following.

Phil
Karen
Cheri
Bill
Gaylon

As you can see, you get back a record set with only the single values and the duplicate values are not shown. What I can’t seem to remember is how to do the opposite of a Distinct(). What do you do when you want to know what values ARE duplicated. On the system I am working on right now I need to know which invoices have more than one shipping date and tracking number. I always have to go out and google on how to do that.

(Hummm… I just noticed that my spell checker says Google should be capitalized, even though I am using it as a verb and not a noun… 🙂 Ok, I’m back from that little rabbit chase now… )

So, I decided that I was just going to post it here so I know where it’s at next time I need it.

Select Name
from Table_Above
group by Name
having COUNT(Name) > 1

If you ran this script on the same table as above, you would get the following results.

Phil
Bill

This script uses the Group By and the Having functions of T-SQL which counts the number of values and then returns the values of the row if it shows up more than one time. It’s a simple little script but I never can seem to remember it when I need it.