How to select distinct on specific columns

Question

I have few columns in a table

Col1, Col2, Col3, Col4

Now I want to select like this

SELECT DISTINCT (Col1, Col2, Col3), Col4

i.e. get the distinct based on only these three colunms.

What about the `Col4` you need to perform some aggregation on this for each distinct value of the other 3 columns? — Mahmoud Gamal, Sep 27 '12 at 11:59
You may get help from this post http://stackoverflow.com/questions/1471250/counting-distinct-over-multiple-columns — Miki Shah, Sep 27 '12 at 12:05

score 8 · Answer 1 · answered Sep 27 '12 at 12:00

8

Just GROUP BY Col1, Col2, Col3 with an aggregate function with the col4 like MAX, MIN, etc .. like so:

SELECT Col1, Col2, Col3, MAX(Col4)
FROM TableName
GROUP BY Col1, Col2, Col3

answered Sep 27 '12 at 12:00

Mahmoud Gamal

78,257
17
139
164

1

That is a varchar field , I just want to have distinct records based on three columns. – Zerotoinfinity Sep 27 '12 at 12:02
@Zerotoinfinite What about `col4` what do you want to do for it for each distinct value for the other rows? – Mahmoud Gamal Sep 27 '12 at 12:06
Col4 is varchar and i can't do any aggregation on that. but that is unique column. I wanted to get unique of 3 columns and just add the fourth column related to that row – Zerotoinfinity Sep 27 '12 at 12:15
@Zerotoinfinite Can you please show me some sample data and how it should look like after that query? – Mahmoud Gamal Sep 27 '12 at 12:20
Can I get like this Col1, Col2 , Col3 and (Col4 in delimiter form) – Zerotoinfinity Sep 27 '12 at 12:30
@Zerotoinfinite Do you mean concatenate values for `col4` into one column? if so, then you can, but there is no built in function to do this in sql server. You have to do this manual using a stored procedure or a user defined function, and if you searched the internet you will find a lot of implementations for such a function. – Mahmoud Gamal Sep 27 '12 at 12:34

score 3 · Accepted Answer · answered Sep 28 '12 at 05:32

From a comment to another answer:

Can I get like this Col1, Col2 , Col3 and (Col4 in delimiter form)

Yes, you can use the for xml path.

select Col1, 
       Col2,  
       Col3, 
       (
        select ',' + T2.Col4
        from YourTable T2
        where T1.Col1 = T2.Col1 and
              T1.Col2 = T2.Col2 and
              T1.Col3 = T2.Col3
        for xml path(''), type
       ).value('substring((./text())[1], 2)', 'varchar(max)') as Col4
from YourTable as T1
group by T1.Col1, T1.Col2, T1.Col3

SQL Fiddle

score 1 · Answer 3 · answered Sep 27 '12 at 12:03

1

The group by and distinct perform almost similar functionality in SQL:

SO both queries below are almost equivalent:

SELECT DISTINCT Col1, Col2, Col3 FROM tbl

SELECT Col1, Col2, Col3 FROM tbl GROUP BY Col1, Col2, Col3

answered Sep 27 '12 at 12:03

RMN

754
9
25
45

1

But I want to have Col4 also in the resultset – Zerotoinfinity Sep 27 '12 at 12:12
Why dont you write an inner query doing distinct of 3 columns and the outer query taking inner 3 and Col4 of matching rows ? – RMN Sep 27 '12 at 12:15

score 0 · Answer 4 · answered Sep 27 '12 at 12:11

0

select distinct ( Convert(varchar(255),Col1) +' '+
Convert(varchar(255),Col2)+' '+Convert(varchar(255),Col3)),
Col4 from clients

answered Sep 27 '12 at 12:11

Miki Shah

815
9
20

How to select distinct on specific columns

4 Answers4