Select row minimum col for each id

Question

I have a table like following

id    id_a    id_b    uds
--------------------------
1      1        3      20
1      2        8      17
2      1        3       5
3      1        1      32
3      2        1       6

What I would need is to get the row with minimum "uds" for each "id". So the result would be:

id    id_a    id_b    uds
--------------------------
1      2        8      17
2      1        3       5
3      2        1       6

Thank you in advance...

It better to share what you've attempted first, then SO's can advise what you doing wrong — Bayeni, Dec 11 '15 at 11:58
Possible duplicate of [Select first row in each GROUP BY group?](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — qxg, Dec 11 '15 at 12:07

gmiley · Answer 1 · 2015-12-11T12:00:45.137

1

Use Min with a group by clause:

select id, id_a, id_b, min(uds) as uds 
from table1 
group by id, id_a, id_b
order by id, id_a, id_b;

However, I should mention this is going to get you all of the items, you need to also specify an aggregate on the other columns, or do not include them.

select id, min(uds) as uds 
from table1 
group by id
order by id;

Judging by your desired output though, the following may be what you want:

select id, max(id_a) as id_a, max(id_b) as id_b, min(uds) as uds 
from table1 
group by id
order by id;

edited Dec 11 '15 at 12:00

answered Dec 11 '15 at 11:56

gmiley

6,531
1
13
25

this would return 2 rows for id = 1. 1 row is expected. – qxg Dec 11 '15 at 11:58
Updated as you were commenting to take that into account. – gmiley Dec 11 '15 at 11:59
what about returning the row(s) with min uds, including id_a, id_b. – qxg Dec 11 '15 at 12:00

score 0 · Answer 2 · answered Dec 11 '15 at 12:11

0

you have to set condition with minimum uds value or you have to decides how many numbers of records you want with minimum uds

answered Dec 11 '15 at 12:11

blkerai

341
1
17

score 0 · Accepted Answer · answered Dec 11 '15 at 12:26

0

Most databases support the ANSI standard window functions. An easy way to do what you want:

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

answered Dec 11 '15 at 12:26

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 4 · answered Dec 11 '15 at 15:41

0

More one way:

select 
    a.*
from 
    #temp a inner join (select id, min(uds) minUds from #temp group by id) b on 
        a.id = b.id 
        and a.uds = b.minUds

answered Dec 11 '15 at 15:41

Max Duarte

181
1
5

Select row minimum col for each id

4 Answers4