CODEWITHSUNDEEP
mysqlinner-join
Ana
Ana

Reputation: 614

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.

Upvotes: 2

Views: 17333

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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

Upvotes: 2

Darshan Mehta
Darshan Mehta

Reputation: 30809

You need to get the latest time per location first, e.g.:

SELECT location, MAX(`datetime`) 
FROM table 
GROUP BY location;

And then, join it with the same table, e.g.:

SELECT t1.*
FROM table t1 JOIN (
    SELECT location, MAX(`datetime`) 
    FROM table 
    GROUP BY location
) a
ON t1.location = a.location AND t1.datetime = a.datetime;

You can order the result by adding ORDER BY t1.value DESC or any other column.

Upvotes: 12

Related Questions