how to select distinct row with minimum value

Question

I want an SQL statement to get the row with a minimum value.

database

ig_idx    ig_team1  ig_team2  rl_league  rl_image          rl_sports   ig_status   cnt
410296   aaaaa      xxxxx     BRA D2     icon_BRA D2.png   7           S           3
410298   aaaaa      xxxxx     BRA D2     icon_BRA D2.png   7           S           3
410299   aaaaa      yyyyy     BRA D2     icon_BRA D2.png   7           S           3
410301   bbbbb      yyyyy     BRA D2     icon_BRA D2.png   7           S           2
410304   bbbbb      yyyyy     BRA D2     icon_BRA D2.png   7           S           2
410306   ccccc      zzzzz     BRA D2     icon_BRA D2.png   7           S           2

How do I select the ids that have the minimum value in the point column? like this...

ig_idx    ig_team1  ig_team2  rl_league  rl_image          rl_sports    ig_status   cnt
410296    aaaaa     xxxxx     BRA D2     icon_BRA D2.png   7            S            3
410301    bbbbb     yyyyy     BRA D2     icon_BRA D2.png   7            S            2
410306    ccccc     zzzzz     BRA D2     icon_BRA D2.png   7            S            2

There is no 'point', however, there is in [this older question](https://stackoverflow.com/questions/15291506/sql-query-to-select-distinct-row-with-minimum-value). — RobertL, Oct 15 '17 at 00:33

score 3 · Accepted Answer · answered Oct 15 '17 at 00:29

3

Use row_number():

select t.*
from (select t.*,
             row_number() over (partition by ig_team order by ig_idx asc) as seqnum
      from t
     ) t
where seqnum = 1;

answered Oct 15 '17 at 00:29

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Oct 15 '17 at 01:30

0

Looks like it's as simple as:

SELECT MIN(ig_idx), ig_team1, ig_team2, r1_league, r1_image, r1_sports, ig_status, cnt
FROM t
GROUP BY ig_team1, ig_team2, r1_league, r1_image, r1_sports, ig_status, cnt

answered Oct 15 '17 at 01:30

kjmerf

4,275
3
21
29

how to select distinct row with minimum value

2 Answers2