SQL Server: Retrieve the duplicate value in a column

Question

Hi all,

How could I filter out the duplicate value in a column with SQL syntax?

Thanks.

Answer 1

distinct would be the keyword to filter douplicates. May be you can explain a little more what you're trying to achieve ?

Answer 2

Depending on how you mean "filter" you could either use DISTINCT or maybe GROUP BY both are used to Remove or Group duplicate entries.

Check the links for more information.

A snippet from the DISTINCT-link above:

SELECT DISTINCT od.productid
FROM [order details] OD

SELECT od.productid
FROM [order details] OD
GROUP BY od.productid

Both of these generally result in the same output.

Answer 3

Use DISTINCT or GROUP BY** clause.

Select DISTINCT City from TableName

OR

Select City from TableName GROUP BY City

Answer 4

A common question, though filtering out suggests you want to ignore the duplicate ones? If so, listing unique values:

SELECT col 
FROM table
GROUP BY col 
HAVING (COUNT(col) =1 )

If you just want to filter so you are left with the duplicates

SELECT col, COUNT(col) AS dup_count
FROM table
GROUP BY col
HAVING (COUNT(col) > 1)

Using http://www.mximize.com/how-to-find-duplicate-values-in-a-table- as a base.