How do I build a query for this?

Question

I have this table:

+--------+---------+-----------+------------+
| bed_id | bed_num | place_id  | bed_date   |
+--------+---------+-----------+------------+
|      1 |     150 |         1 | 1244344305 |
|      2 |     250 |         1 | 1244345469 |
|      3 |     145 |         2 | 1244348496 | 
|      3 |     146 |         2 | 1244348497 | 
+--------+---------+-----------+------------+

I want to select all the unique place_ids with most recent date of bed_num so it should return something like below:

+--------+---------+-----------+------------+
| bed_id | bed_num | place_id  | bed_date   |
+--------+---------+-----------+------------+
|      2 |     250 |         1 | 1244345469 |
|      3 |     146 |         2 | 1244348497 | 
+--------+---------+-----------+------------+

I have tried mixing GROUP BY and ORDER BY using distinct(place_id) AND max(bed_date)

But failing!

Thanks for any help! :)

Answer 1

Since you want to get some aggregated data (place_id, bed date), and other non-aggregate data (bed_id and bed_num), you need to use a subselect to do the aggregate, then join on it as if it was an anonymous view:

SELECT bed_id, bed_num, place_id, bed_date
FROM beds
INNER JOIN (SELECT place_id, MAX(bed_date) FROM beds GROUP BY place_id) max_dates
ON max_dates.place_id = beds.place_id AND max_dates.bed_date = beds.bed_date

Otherwise, your aggregate "group by" will apply to all your columns, and you'll end up with a (potentially random) bed_id and bed_num from the aggregate