Get record with min value of a group

Question

I'm trying to do the opposite of this question

I need to get the minimum value out of a grouped record, so, taking the same information from the linked question, from

ID | Person | Group | Age
---
1  | Bob    | 1     | 32  
2  | Jill   | 1     | 34  
3  | Shawn  | 1     | 42  
4  | Jake   | 2     | 29  
5  | Tom    | 2     | 28  
6  | Paul   | 2     | 36  
7  | Laura  | 2     | 39  
8  | Julie  | 2     | 28

Desired result set:

1  | Bob    | 1     | 32    
5  | Tom    | 2     | 28

I tried by joining the table on herself with opposite condition (< instead of >) but it's not working.

Duplicates are not allowed, if two persons have the same age, the one with the lowest ID has to be returned.

Note that group is a reserved word, making it a decidedly poor choice for a column identifier. (And - unless it's a table of the deceased - you don't really store age, do you!!) — Strawberry, Aug 01 '17 at 12:42
Well I just copied the example provided in the linked question. I know it's a reserved word. — Cusy, Aug 01 '17 at 13:00

Gordon Linoff · Answer 1 · 2017-08-01T15:23:43.780

4

I would do this using where and a correlated subquery:

select p.*
from persons p
where p.age = (select min(p2.age) from persons p2 where p2.group = p.group);

or:

select p.*
from persons p
where p.age = (select p2.age
               from persons p2
               where p2.group = p.group
               order by p2.age desc
               limit 1
              );

This easily extends to multiple keys by using id:

select p.*
from persons p
where p.id = (select p2.id
               from persons p2
               where p2.group = p.group
               order by p2.age desc, id asc
               limit 1
              );

edited Aug 01 '17 at 15:23

answered Aug 01 '17 at 12:38

Gordon Linoff

1,242,037
58
646
786

What's the main difference between this and joining the table itself? Different performance? – Cusy Aug 01 '17 at 12:55
@Cusy For something like this there isn't much difference between this and a self-join. Performance should be the same, since the optimizer will take over and rewrite in the more efficient manner. For a short subquery like this it comes down to readability on whether to keep as is or convert to a self-join. – yanman1234 Aug 01 '17 at 13:02
@Cusy . . . I find that this is easier to follow. In MySQL and other databases, I think the performance of the two should be similar, particularly with an index on `persons(group, age)`. – Gordon Linoff Aug 01 '17 at 13:02
It still retrieves duplicates (IE: add another person in group 2 with 29 years). Any way to filter those out? – Cusy Aug 01 '17 at 13:04
@Cusy Edit your question with the result you are now receiving. – yanman1234 Aug 01 '17 at 13:25
Edited with more information. This should also avoid the "Group by" wrong answer. – Cusy Aug 01 '17 at 13:30

score 0 · Answer 2 · answered Aug 01 '17 at 13:58

Here's one way (I renamed one of the columns in order to preserve my sanity):

    SELECT a.* 
      FROM my_table a 
      JOIN 
         ( SELECT MIN(id) id 
             FROM my_table x 
             JOIN 
                ( SELECT group_id, MIN(age) age FROM my_table GROUP BY group_id) y 
               ON y.group_id = x.group_id 
              AND y.age = x.age 
            GROUP 
               BY x.group_id
                , x.age
         ) b 
        ON b.id = a.id;

Get record with min value of a group

2 Answers2