Select DISTINCT multiple columns

Question

I am having trouble in retrieving DISTINCT records. Scenario is as follow: Right now my query is

Select a,b,c from TABLE_NAME
(COMPLEX_INNER JOIN LOGIC)

I want records where first two column values should be unique. I know this can be done by

GROUP BY

Clause. So the query will become

Select a,b,c from TABLE_NAME
(COMPLEX_INNER JOIN LOGIC)
GROUP BY a,b

But as c is not appearing in aggregate function or group by SQL server is giving following error:

Column 'c' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

how can you select 2 column distinct when you're selecting 3 columns.... isn't it against logical use of distinct/group by ??? — Umer
– Umer, Commented Feb 9, 2012 at 7:08

Mikael Eriksson · Accepted Answer · 2012-02-09 07:08:44Z

1

You can put your query in a CTE and use the row_number() function to figure out what rows to fetch.
Something like this:

with C as
(
  Select a,b,c,
         row_number() over(partition by a, b order by SomeColumn) as rn
  from TABLE_NAME
  --(COMPLEX_INNER JOIN LOGIC)
)
select a, b, c
from C
where rn = 1

Working sample:

declare @T table
(
  a int,
  b int,
  c int
)

insert into @T values
(1, 1, 1),
(1, 1, 2),
(2, 2, 1),
(2, 2, 2)

;with C as
(
  select a, b, c,
         row_number() over(partition by a, b order by c) as rn
  from @T         
)
select a, b, c
from C
where rn = 1

Result:

a           b           c
----------- ----------- -----------
1           1           1
2           2           1

answered Feb 9, 2012 at 7:08

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

Comments

Rashmi Kant Shrivastwa · Accepted Answer · 2012-02-09 07:08:39Z

0

use like this

select c,q.a,q.b from TABLE_NAME inner join 
(
Select a,b from TABLE_NAME
(COMPLEX_INNER JOIN LOGIC)
GROUP BY a,b) q 
on q.a=TABLE_NAME.a

answered Feb 9, 2012 at 7:08

Rashmi Kant Shrivastwa

1,1778 silver badges17 bronze badges

Collectives™ on Stack Overflow

Select DISTINCT multiple columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related