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

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, 2012 at 19:52

Gregory Kaczmarczyk

91 bronze badge

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related