Max Williams Max Williams - 4 months ago 10
SQL Question

Get values from first sorted member of grouped(?) sql query

I feel like this is obvious but i'm struggling. Must be because it's a monday.

I have a

licenses
table in MySQL which has fields
id (int)
,
start_date (date)
,
licensable_id (int)
,
licensable_type (string)
and
fixed_end_point (boolean)
.

I want to get all licenses where the start date is equal to or less than today, group them by
licensable_id and licensable_type
, and then get the most recently starting one so I can get the
fixed_end_point
field out of it, along with
licensable_id
and
licensable_type
.

This is what i'm trying:

SELECT licensable_id, licensable_type, fixed_end_point
FROM licenses
WHERE start_date <= "2016-08-01"
GROUP BY licensable_id, licensable_type
ORDER BY start_date desc;


At the moment, the
ORDER BY
field seems to be being ignored, and it's just returning the values from the first license for each group, rather than the most recent. Can anyone see what I'm doing wrong? Do I need to make a nested query?

Answer

You shouldn't be thinking about this as a group by. You want to select the most recent start_date for each license, given the constraints in the question. One method uses a correlated subquery:

select l.*
from licenses l
where l.start_date = (select max(l2.start_date)
                      from licenses l2
                      where l2.licensable_id = l.licensable_id and
                            l2.licensable_type = l2.licensable_type and
                            l2.start_date <= '2016-08-01'
                     );
Comments