DrLizzard DrLizzard - 6 months ago 15
MySQL Question

How do I select rows with MAX(datetime) and also get min values for a column?

My table is like this (np_capacity):

id tower datetime capacity
---|----|---------------------|----------
1 | A | 2016-05-29 09:02:41 | 34676
2 | B | 2016-05-29 09:02:41 | 10736
5 | C | 2016-05-29 09:02:41 | 55664
3 | D | 2016-05-29 09:02:41 | 32622
4 | A | 2016-05-29 13:08:38 | 5474
6 | B | 2016-05-29 13:08:38 | 20692
7 | C | 2016-05-29 13:08:38 | 134802
8 | D | 2016-05-29 13:08:38 | 4754


I want to select all the tower with the max date then for those towers I also want the min capacity value in the table.

Result would be:

id tower datetime capacity MinCapacity
---|----|---------------------|----------|-------------
4 | A | 2016-05-29 13:08:38 | 5474 | 5474
6 | B | 2016-05-29 13:08:38 | 20692 | 10736
7 | C | 2016-05-29 13:08:38 | 134802 | 55664
8 | D | 2016-05-29 13:08:38 | 4754 | 4754


What I have is this but it doesnt always give my the correct min values.

SELECT npc.*, groupedcap.MinCapacity
FROM np_capacity npc
INNER JOIN
(SELECT tower, MAX(date) AS MaxDate
FROM np_capacity
GROUP BY tower) groupednpc
ON npc.tower = groupednpc.tower
INNER JOIN
(SELECT tower, MIN(capacity) AS MinCapacity
FROM np_capacity
GROUP BY tower) groupedcap
ON npc.tower = groupedcap.tower
AND npc.date = groupednpc.MaxDate

Answer

You can use a subselect to calculate the min capacity, and the max date. Then, join with the table to get other fields.

select npc.*, calc.minCapacity
from (
    select tower, max(datetime) maxDate, min(capacity) minCapacity
    from np_capacity
    group by tower
) calc
join np_capacity npc on (npc.tower = calc.tower
                         and npc.datetime = npc.maxDate)

This request select all towers, and for each the maxdatetime and mincapacity.

If you want just the towers with the maxdatetime, you can use :

select npc.*, (select min(c2.capacity) from np_capacity c2 
               where c2.tower = npc.tower) minCapacity
from (select max(datetime) maxDatetime from np_capacity) c1
join np_capacity npc on (npc.datetime = c1.maxDatetime)