MySQL Group By order

I'm trying to GROUP BY a column and have the row with the latest
timestamp to be returned. Example:

TABLE Test

id | text | timestamp
1 , 'A' , 2008-03-01
1 , 'B' , 2008-03-26

SELECT *
FROM Test
GROUP BY id

RESULTS:
1 , 'A' , 2008-03-01

I need the results to be the row with the latest timestamp:
1 , 'B' , 2008-03-26

Of course a nexted query can produce this result but this will create
tempory table which will be inefficiant. Is there a way to do with
without a nested query. By perhaps somehow ordering before the group
by?

On Mar 26, 7:52 pm, "ryanm...@yahoo.com" <ryanm...@yahoo.com> wrote:
> I'm trying to GROUP BY a column and have the row with the latest
> timestamp to be returned. Example:
>
> TABLE Test
>
> id | text | timestamp
> 1 , 'A' , 2008-03-01
> 1 , 'B' , 2008-03-26
>
> SELECT *
> FROM Test
> GROUP BY id
>
> RESULTS:
> 1 , 'A' , 2008-03-01
>
> I need the results to be the row with the latest timestamp:
> 1 , 'B' , 2008-03-26
>
> Of course a nexted query can produce this result but this will create
> tempory table which will be inefficiant. Is there a way to do with
> without a nested query. By perhaps somehow ordering before the group
> by?

Look for "strawberry query" in this NG.

On Mar 26, 5:47*pm, strawberry <zac.ca...@gmail.com> wrote:
> On Mar 26, 7:52 pm, "ryanm...@yahoo.com" <ryanm...@yahoo.com> wrote:
>
>
>
>
>
> > I'm trying to GROUP BY a column and have the row with the latest
> > timestamp to be returned. Example:
>
> > TABLE Test
>
> > id | text | timestamp
> > 1 , 'A' , 2008-03-01
> > 1 , 'B' , 2008-03-26
>
> > SELECT *
> > FROM Test
> > GROUP BY id
>
> > RESULTS:
> > 1 , 'A' , 2008-03-01
>
> > I need the results to be the row with the latest timestamp:
> > 1 , 'B' , 2008-03-26
>
> > Of course a nexted query can produce this result but this will create
> > tempory table which will be inefficiant. Is there a way to do with
> > without a nested query. By perhaps somehow ordering before the group
> > by?
>
> Look for "strawberry query" in this NG.- Hide quoted text -
>
> - Show quoted text -

Thanks strawberry you're the man. For anyone else that may be stuck
searching besides know as the Strawberry Query thanks to Strawberry
shedding some light on it there are references to Group-wise
Maximum.

http://dev.mysql.com/doc/refman/5.0/...group-row.html