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
– taro, Commented Sep 10, 2010 at 7:44

cjk · Accepted Answer · 2010-09-10 07:45:50Z

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, 2010 at 7:45

cjk

46.6k9 gold badges83 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kevchadders Over a year ago

+1 Nice and simple. Think its also worth mentioning that you got around the duplicate IDs, by using MAX.

brayancastrop Over a year ago

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.

cjk Over a year ago

@brayancastrop - you can use MAX(any_column) or MIN(any_column) to select an individual value for that column

Gregory Kaczmarczyk · Accepted Answer · 2012-05-10 19:52:54Z

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;

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related