On Nov 3, 3:48 pm, NickPick <dickreu...@yahoo.com> wrote:
> 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.
> On Nov 3, 3:48 pm, NickPick <dickreu...@yahoo.com> wrote:
> > 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
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
> On Nov 3, 10:06 pm, toby <t...@telegraphics.com.au> wrote:
> > On Nov 3, 3:48 pm, NickPick <dickreu...@yahoo.com> wrote:
> > > 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
> 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
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.)
>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)'.
> On Nov 3, 6:43 pm, NickPick <dickreu...@yahoo.com> wrote:
> > On Nov 3, 10:06 pm, toby <t...@telegraphics.com.au> wrote:
> > > On Nov 3, 3:48 pm, NickPick <dickreu...@yahoo.com> wrote:
> > > > 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
> > 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
> 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.)
This only outputs the last value of today for some reason... Any idea what could be the problem?
> On Nov 4, 3:05 am, toby <t...@telegraphics.com.au> wrote:
> > On Nov 3, 6:43 pm, NickPick <dickreu...@yahoo.com> wrote:
> > > On Nov 3, 10:06 pm, toby <t...@telegraphics.com.au> wrote:
> > > > On Nov 3, 3:48 pm, NickPick <dickreu...@yahoo.com> wrote:
> > > > > 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
> > > 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
> > 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.)
> This only outputs the last value of today for some reason... Any idea > what could be the problem?
> >"NickPick" <dickreu...@yahoo.com> wrote in message > >news:57ccc1f9-f307-4a7b-ad11-de2db0287953@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)'.
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.
|
