Gordon Stewart wrote:
> if I have
>
> Sat 15th 1 a.m, name1, 12
> Sat 15th 7 a.m, name1, 12
> Sat 15th 1 p.m, name1, 14
> Sat 15th 7:p.m, name1, 14
> Sun 16th 1:a.m, name1, 15
> Sun 16th 7:a.m, name1, 16
> Sun 16th 1:p.m, name1, 18
> Sun 16th 7:p.m, name1, 18
> Mon 17th 1 a.m., name1, 19
>
> (i'll use the time() function/value - Ive just used words to make it clear)
>
> In the above, I only want the last value of each day - In this example :-
>
> Sat 15th 11:p.m, name1, 14
> Sun 16th 11:p.m, name1, 18
> Mon 17th 1 a.m., name1, 19
I don't see a 11:00 PM for neither Sat 15th nor Sun 16th in you data
above. If I understand your problem properly, this should be your output:
Sat 15th 7:p.m, name1, 14
Sun 16th 7:p.m, name1, 18
Mon 17th 1 a.m., name1, 19
because 7:00 PM is the last time before midnight on both Sat 15th and
Sun 16th. And because there is only one entry for Mon 17th, that row is
returned.
If that's your case, your problem can be solved with a subquery like
this one which is really a kind of a foreach behind the scenes:
SELECT name, value, FROM_UNIXTIME(time)
FROM table_name t1
WHERE time = (SELECT MAX(time)
FROM table_name t2
WHERE
FROM_UNIXTIME(t1.time, "%Y-%m-%d") =
FROM_UNIXTIME(t2.time, "%Y-%m-%d"));
Note that
SELECT MAX(time), name, value FROM table_name GROUP BY
FROM_UNIXTIME(time, "%Y-%m-%d")
will not work as the name and value columns are not guaranteed to be the
same within a given group, i.e. within the same day. MySQL therefore
returns a random name and a random value for each group, which may not
necessarily correspond to the row that holds the largest time for that
group.
Hence the need for the subquery.
--
Rajesh
