CODEWITHSUNDEEP
sqlsql-servert-sql
Tony
Tony

Reputation: 12695

T-SQL How to select rows without duplicate values from one column?

I have a table with 2 columns ID, ID_PROJ_CSR

The content of that table is:

ID     ID_PROJ_CSR
------------------
747    222   <
785    102
786    222   <
787    223
788    224

I want to select the ID, but if any value from ID_PROJ_CSR is a duplicate, I need to select any ID of the rows that contains that duplicate value (in that example, select ID 747 OR 786

I try:

SELECT * FROM my_table tab 
WHERE tab.id_proj_csr = (SELECT TOP 1 id_proj_csr
                         FROM my_table mt
                         WHERE mt.id_proj_csr = tab.id_proj_csr)

Upvotes: 5

Views: 26747

Answers (2)

Gregory Kaczmarczyk
Gregory Kaczmarczyk

Reputation: 9

Here's the case of omitting anything that has a duplicate value, so you'll only get rows that don't have duplicates:

SELECT *
FROM my_table
GROUP BY ID_PROJ_CSR
HAVING count(ID_PROJ_CSR) = 1;

Upvotes: 1

cjk
cjk

Reputation: 46415

You need to GROUP BY:

SELECT MAX(ID) as [ID], ID_PROJ_CSR
FROM my_table
GROUP BY ID_PROJ_CSR

Upvotes: 16

Related Questions