CODEWITHSUNDEEP
sqldistinct
user8512672
user8512672

Reputation: 11

SQL Query distinct rows + minimum value

Sorry for being noob, but need some help in creating this query:

table

id  | C  | S  | T   
--- |--- |--- |---  
1   |C1  |S1  |DA   
2   |C1  |S1  |DA   
3   |C1  |S1  |DB   
4   |C2  |S3  |DA   
5   |C1  |S2  |DC

Need to show first row for each unique C (with smallest id)

So end result will look like (C1 and C2 are unique, C1 shows only first line as ID is 1):

    id  | C  | S  | T   
    --- |--- |--- |---  
    1   |C1  |S1  |DA   
    4   |C2  |S3  |DA

Can you please help? I tried to play around distinct and inner join but cant get any good results.

Upvotes: 0

Views: 1087

Answers (2)

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36591

Using ROW_NUMBER() function with partition by you will be able to create groups which will give row numbers to each group based on values in C columns starting with 1 to n. To select the first row you can use RowNum = 1;

With TableCTE
As
(
SELECT id, C, S, T, ROW_NUMBER()(Partition by C Order by Id) as RowNum
From TableName
)

SELECT * FROM TableCtE 
WHERE RowNum = 1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I would recommend row_number() for this:

select id, c, s, t
from (select t.*, row_number() over (partition by c order by id) as seqnum
      from t
     ) t
where seqnum = 1

Upvotes: 1

Related Questions