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

Question

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)

Maybe something like `select ID from my_table where ID_PROJ_CSR IN (select ID_PROJ_CSR from my_table group by ID_PROJ_CSR HAVING count(ID) > 1)` — taro, Sep 10 '10 at 07:44

score 16 · Accepted Answer · answered Sep 10 '10 at 07:45

16

You need to GROUP BY:

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

answered Sep 10 '10 at 07:45

cjk

45,739
9
81
112

1

+1 Nice and simple. Think its also worth mentioning that you got around the duplicate IDs, by using MAX. – kevchadders Sep 10 '10 at 07:48
What if the table have more columns to retrieve with? i have a similar issue, and the given response works if i just want id and id_proj_csr, but when i need more columns to be retrieves it starts asking to add them to the group by and start retrieving incorrect records. – brayancastrop Sep 22 '15 at 04:42
@brayancastrop - you can use MAX(any_column) or MIN(any_column) to select an individual value for that column – cjk Sep 22 '15 at 10:17

score 1 · Answer 2 · answered May 10 '12 at 19:52

1

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;

answered May 10 '12 at 19:52

Gregory Kaczmarczyk

9
1

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

2 Answers2

Linked