CODEWITHSUNDEEP
mysql
Benvaulter
Benvaulter

Reputation: 249

MySQL return last value per day

I am having the following dataset:

+----+------------+-------+-------+
| ID |    Date    | Time  | Value |
+----+------------+-------+-------+
|  1 | 2015-01-01 | 14:00 |     1 |
|  2 | 2015-01-01 | 16:00 |     2 |
|  3 | 2015-01-02 | 14:00 |    15 |
|  3 | 2015-01-02 | 17:00 |    12 |
+----+------------+-------+-------+

Now I want to return the last value for each day and the result should look like that:

+----+------------+-------+-------+
| ID |    Date    | Time  | Value |
+----+------------+-------+-------+
|  2 | 2015-01-01 | 16:00 |     2 |
|  3 | 2015-01-02 | 17:00 |    12 |
+----+------------+-------+-------+

I am using the following code that somehow delivers close results:

SELECT DISTINCT     t.id,
                    t.date,
                    t.time,
                    t.balance
FROM db1 t
JOIN (
      SELECT MAX(tt.time) 'maxtime' 
      FROM db1 tt
      GROUP BY tt.date) m ON m.maxtime = t.time

This now returns the desired last value for a majority of the dates. However, this code also deliveres some random lines that are not the last value.

Many thanks already now for your help, cheers

Upvotes: 2

Views: 845

Answers (3)

dfunes
dfunes

Reputation: 1

Edit: Have you got the fields date and time as unique? If you haven't, you have to add the distinct.

I've seen other answers and I think you don't need a JOIN for it:

SELECT distinct t.date, t.time 
FROM table t 
WHERE t.`time` = (
    SELECT MAX(time)
    FROM table t2 
    WHERE t.date = t2.date
);

Performance without JOIN:

starting                        0.000036
Waiting for query cache lock    0.000006
checking query cache for query  0.000015
checking privileges on cached   0.000005
checking permissions            0.000011
checking permissions            0.000007
sending cached result to clien  0.000113
logging slow query              0.000007
cleaning up                     0.000005

Performance with JOIN: 

starting                        0.000036
Waiting for query cache lock    0.000006
checking query cache for query  0.000013
checking privileges on cached   0.000006
checking permissions            0.000011
checking permissions            0.000005
sending cached result to clien  0.000121
logging slow query              0.000007
cleaning up                     0.000005

Upvotes: 0

Rahul
Rahul

Reputation: 77866

You can try like below as already commented. See a demo fiddle here http://sqlfiddle.com/#!9/acbb2/4

SELECT              t.id,
                    t.date,
                    t.time,
                    t.`Value`
FROM db1 t
JOIN (
      SELECT `Date`, MAX(`time`) 'maxtime' 
      FROM db1
      GROUP BY `date`) m 
      ON m.maxtime = t.`time` 
      AND m.`Date` = t.`Date`;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You are close. You just need to check for the date:

SELECT t.id, t.date, t.time, t.balance
FROM db1 t JOIN
     (SELECT tt.date, MAX(tt.time) as maxtime
      FROM db1 tt
      GROUP BY tt.date
     ) m
     ON m.maxtime = t.time AND m.date = t.date;
-------------------------------^

SELECT DISTINCT is not necessary unless you think that two records might have exactly the same time.

Upvotes: 2

Related Questions