Search code examples
mysqlinner-join

SELECT latest record group by one column

I have this table from which I have to select the latest row for each location

|   location   |    parameter   |   datetime                | value |
---------------------------------------------------------------------
|   Location 1 |    P1          |   2017-09-13 05:00:00     | 0.68  |
|   Location 1 |    P2          |   2017-09-13 05:00:00     | 6     |
|   Location 1 |    P3          |   2017-09-13 06:00:00     | 19    |
|   Location 1 |    P4          |   2017-09-13 06:00:00     | 1     |
|   Location 2 |    P1          |   2017-09-13 05:00:00     | 0.1   |
|   Location 2 |    P2          |   2017-09-13 05:00:00     | 2     |
|   Location 2 |    P3          |   2017-09-13 06:00:00     | 26    |
|   Location 2 |    P5          |   2017-09-13 06:00:00     | 7.9   |
|   Location 2 |    P4          |   2017-09-13 07:00:00     | 0     |
|   Location 3 |    P1          |   2017-09-13 04:00:00     | 0.47  |
|   Location 3 |    P2          |   2017-09-13 05:00:00     | 1     |
|   Location 3 |    P3          |   2017-09-13 04:00:00     | 25    |
|   Location 3 |    P5          |   2017-09-14 05:00:00     | 3.8   |
---------------------------------------------------------------------

enter image description here

I have tried the following queries but both are not returning the latest data for each location.

SELECT * FROM mytable WHERE 
datetime in (SELECT max(datetime) FROM myTable Group by location) 
group by location;

AND

SELECT * FROM myTable AS t1
INNER JOIN
(
   SELECT MAX(datetime) AS maxDate
   FROM myTable
   GROUP BY location
) AS t2  ON t1.datetime = t2.maxDate group by t1.location;

Both queries return the wrong data for location 2.

Once this is done I also want to add another condition in the query where the latest dates are same then order by value desc and select the record with highest value.

The expected output is

|   location   |    parameter   |   datetime                | value |
---------------------------------------------------------------------
|   Location 1 |    P3          |   2017-09-13 06:00:00     | 19    |
|   Location 2 |    P4          |   2017-09-13 07:00:00     | 0     |
|   Location 3 |    P5          |   2017-09-14 05:00:00     | 3.8   |
---------------------------------------------------------------------

but the queries I am trying returns the wrong date for Location 2.

Any help is appreciated.

Solution

  • You can use variables for this:

    SELECT location, parameter, datetime, value
FROM (
   SELECT location, parameter, datetime, value,
          @seq := IF(@loc = location, @seq + 1,
                     IF(@loc := location, 1, 1)) AS seq
   FROM mytable
   CROSS JOIN (SELECT @seq := 0, @loc = '') AS vars
   ORDER By location, datetime desc, value desc) AS t
WHERE t.seq = 1

    The inner query has an ORDER BY clause that returns the required latest-per-group record first within its own slice. The variable @seq is set to 1 for this first record using the logic implemented by the IF functions. The outer query simply filters the derived table to get the expected record for each location slice.

    Demo here