How to group by lowest value in field

Question

I have a query in mysql that returns a resultset like this

ID1     ID2     string      date
14071   3031324 string 1    2016-08-10 21:29:39
14071   3054168 string 2    2016-08-10 21:33:26
14071   3054169 string 3    2016-08-10 21:33:26
14074   2938174 string 2    2016-08-10 21:14:48
14074   2938175 string 3    2016-08-10 21:14:48
14074   2938176 string 1    2016-08-10 21:14:48
14093   2954386 string 2    2016-08-10 21:17:22
14093   3035338 string 3    2016-08-10 21:30:19

I need to filter this to ruturn only the lins with the lowest ID2 for each ID1

14071   3031324 string 1    2016-08-10 21:29:39
14074   2938174 string 2    2016-08-10 21:14:48
14093   2954386 string 2    2016-08-10 21:17:22

thanks

Tables have _columns_, not fields. A date has a year _field_, a month _field_ etc. — jarlh, Mar 25 '21 at 13:39
@jarlh not sure how this comment contributes to the discussion — user3174311, Mar 25 '21 at 14:51

cwalvoort · Answer 1 · 2021-03-25T13:30:20.097

2

You can RANK() the records partitioning as needed and selecting only the lowest rank:

SELECT ID1, ID2, string, date
FROM (
    SELECT ID1, ID2, string, date, RANK() over ( PARTITION BY ID1, ORDER BY ID2 ASC ) rank
    FROM {your_table}
)
WHERE rank = 1

edited Mar 25 '21 at 13:30

answered Mar 25 '21 at 13:27

cwalvoort

1,851
1
18
19

score 0 · Accepted Answer · answered Mar 25 '21 at 13:26

0

You can do:

select *
from t
where (id1, id2) in (select id1, min(id2) from t group by id1)

answered Mar 25 '21 at 13:26

The Impaler

45,731
9
39
76

How to group by lowest value in field

2 Answers2