CODEWITHSUNDEEP
sql-serveroracle-database
user3049869
user3049869

Reputation: 13

Selecting a distinct row for a group

Can anyone please tell me how to select the record with the minimum value in column C3 for each distinct value in column C1, but if the values are same in C3, then the record with the value 'x' in column c2 should be selected. For example for the values inserted below I want to see the first and the third row. Many thanks.

CREATE TABLE Test.tst (
    C1  nvarchar(3) NOT Null,
    C2  nvarchar(3) NOT Null,
    C3  int not null)

Insert into test.tst
select 'A', 'x', 2 union
select 'A', 'y', 2 union
select 'B', 'x', 1 union
select 'B', 'y', 2

Upvotes: 0

Views: 75

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271091

The general answer to your question is to use row_number() with a clever order by:

select c1, c2, c3
from (select t.*,
             row_number() over (partition by c1
                                order by c3 asc, (case when c2 = 'x' then 1 else 0 end) desc
                               ) as seqnum
      from tst t
     ) t
where seqnum = 1;

EDIT:

Here is a working SQL Fiddle.

Upvotes: 3

mituw16
mituw16

Reputation: 5260

Something like this should work 

select C1, min(C2) as C2, min(C3) as C3 from test group by C1

EDIT: Added SQL Fiddle

Upvotes: 0

Related Questions