Max Williams Max Williams - 1 year ago 66
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

table in MySQL which has fields
id (int)
start_date (date)
licensable_id (int)
licensable_type (string)
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
field out of it, along with

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
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 Source

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'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download