last item of each day
NickPick
show that last entry of each day only, for the last 30 days. How can I
do this?
Any help is appreciated
toby
> I have a list with entries and each entry has a timestamp. I'd like to
> show that last entry of each day only, for the last 30 days. How can I
> do this?
SELECT *
FROM t JOIN (
SELECT MAX(ts) AS max_ts -- `ts` is the timestamp column
FROM t
GROUP BY DATE(ts)
WHERE DATEDIFF(NOW(), ts) <= 30
) Day ON t.ts = Day.max_ts
This will give you multiple entries where timestamps are equal, so you
may want to use the auto-incrementing id instead (if you have one),
i.e. MAX(id) in the same group.
>
> Any help is appreciated
NickPick
I get this error message:
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'WHERE DATEDIFF(NOW(), timestamp) <= 30 ) Day ON trades.timestamp
= Day.max_ts ' at line 6
this is what I used (table name: trades, timestamp column is called
timestamp)
SELECT *
FROM trades JOIN (
SELECT MAX(timestamp) AS max_ts
FROM trades
GROUP BY DATE(timestamp)
WHERE DATEDIFF(NOW(), timestamp) <= 30
) Day ON trades.timestamp = Day.max_ts
toby
Sorry, I had the WHERE and GROUP BY clauses swapped. Perils of pasting
untested code!
The WHERE condition as given seems to have a bug where only part of
the oldest day is considered. Try: WHERE `timestamp` >= CURDATE() -
INTERVAL 30 DAY -- this also has the benefit of being an indexable
range condition.
(Also, you might reconsider the column name timestamp, being the name
of a type.)
Andrew C.
"NickPick" <dickr...@yahoo.com> wrote in message
news:57ccc1f9-f307-4a7b...@m38g2000yqd.googlegroups.com...
I find it counter-intuitive that a query of the following form doesn't work:
SELECT *, MAX(tstamp) FROM tbl GROUP BY DATE(tstamp);
In my ideal-world imagination, MySQL would provide the '*' from the same row
as it found the 'MAX(tstamp)'.
Alas! ;-)
A.
Erick T. Barkhuis
I think now it's my turn to say: you want the Strawberry Query.
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
--
Erick
"The secret of success is sincerity. Once you can fake that, you've got
it made."
NickPick
This only outputs the last value of today for some reason... Any idea
what could be the problem?
thanks
Captain Paralytic
Erick has pointed you at the correct answer.
Andrew C.
"Erick T. Barkhuis" <erick....@ardane.c.o.m> wrote in message
news:7lcni1F...@mid.individual.net...
> Andrew C.:
>>
>>"NickPick" <dickr...@yahoo.com> wrote in message
>>news:57ccc1f9-f307-4a7b...@m38g2000yqd.googlegroups.com
>>...
>>>I have a list with entries and each entry has a timestamp. I'd like
>>>to show that last entry of each day only, for the last 30 days. How
>>>can I do this?
>>>
>>>Any help is appreciated
>>
>>I find it counter-intuitive that a query of the following form
>>doesn't work:
>>
>>SELECT *, MAX(tstamp) FROM tbl GROUP BY DATE(tstamp);
>>
>>In my ideal-world imagination, MySQL would provide the '*' from the
>>same row as it found the 'MAX(tstamp)'.
>>
>
> I think now it's my turn to say: you want the Strawberry Query.
> http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
Me? No, I don't want it... You'll have to find someone else who wants it...
;-)
A.
strawberry
wrote:
> Andrew C.:
>
>
>
>
>
> >"NickPick" <dickreu...@yahoo.com> wrote in message
> >news:57ccc1f9-f307-4a7b...@m38g2000yqd.googlegroups.com
> >...
> >>I have a list with entries and each entry has a timestamp. I'd like
> >>to show that last entry of each day only, for the last 30 days. How
> >>can I do this?
>
> >>Any help is appreciated
>
> >I find it counter-intuitive that a query of the following form
> >doesn't work:
>
> >SELECT *, MAX(tstamp) FROM tbl GROUP BY DATE(tstamp);
>
> >In my ideal-world imagination, MySQL would provide the '*' from the
> >same row as it found the 'MAX(tstamp)'.
>
>
> --
> Erick
>
> "The secret of success is sincerity. Once you can fake that, you've got
> it made."
Ah, but which one IS the strawberry query? ;-)
Erick T. Barkhuis
>On Nov 4, 7:07�am, "Erick T. Barkhuis" <erick.use-...@ardane.c.o.m>
>wrote:
>> Andrew C.:
>>
>>
>>
>>
>>
>> >"NickPick" <dickreu...@yahoo.com> wrote in message
>>
>>>news:57ccc1f9-f307-4a7b...@m38g2000yqd.googlegroups.
>>com >... >>I have a list with entries and each entry has a
>>timestamp. I'd like >>to show that last entry of each day only,
>>for the last 30 days. How >>can I do this?
>>
>> >>Any help is appreciated
>>
>> >I find it counter-intuitive that a query of the following form
>> >doesn't work:
>>
>> >SELECT *, MAX(tstamp) FROM tbl GROUP BY DATE(tstamp);
>>
>> >In my ideal-world imagination, MySQL would provide the '*' from
>>the >same row as it found the 'MAX(tstamp)'.
>>
>> I think now it's my turn to say: you want the Strawberry
>>Query.http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-r...
>Ah, but which one IS the strawberry query? ;-)
Nobody knows.
It's one of the best-kept secrets of this group. Many have already
tried to discover the Real Strawberry Query, but the efforts have been
unsuccessful so far....and very likely, so will they be in the future.
Of course, there's plenty of imitation stuff around.
--
Erick