List:General Discussion« Previous MessageNext Message »
From:Tanner Postert Date:July 18 2006 6:44pm
Subject:Re: Distinct problem
View as plain text  
the below query worked great in mysql 3.23, but we just moved to 5.0 and it
broke, i can see that the join rules changed in 5.0, but i can't get the
right syntax to make this query work. any help would be appreciated.

On 6/28/06, Peter Brawley <peter.brawley@stripped> wrote:
>
>  Tanner
>
>
> >I am trying to group my results by the last activity on each row, my
> query
> >looks like this
> >select text, dt, item_id from table
> >where ....
> >group by item_id
> >order by dt DESC
>
> SELECT t1.item_id, t1.dt, t1.text
> FROM table AS t1
> LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt < t2.dt
> WHERE t2.item_id IS NULL;
>
> PB
>
> -----
>
>
> Tanner Postert wrote:
>
> The situation is somewhat hard to describe, so please bare with me:
>
> I am trying to group my results by the last activity on each row, my query
>
> looks like this
>
> select text, dt, item_id from table
> where ....
> group by item_id
> order by dt DESC
>
> here is an example record set.
>
>
> text1,2006-06-28 10:00:00,4
> text2,2006-06-28 10:15:00,4
> text3,2006-06-28 10:30:00,8
> text4,2006-06-28 11:00:00,8
>
> the results from the above query would be:
>
> text1,2006-06-28 10:00:00,4
> text3,2006-06-28 10:30:00,8
>
> my problem is that i want the other item to show up. the item with the
> most
> recent DT. it is doing the grouping before it does the ordering. how do i
> specify that I want to see the most recent info when it does the group?
>
> thanks in advance.
>
> Tanner
>
> ------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.9.5/377 - Release Date: 6/27/2006
>
>
>
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.9.5/377 - Release Date: 6/27/2006
>
>
>

Thread
Distinct problemTanner Postert28 Jun
  • Re: Distinct problemDan Buettner28 Jun
  • Re: Distinct problemPeter Brawley29 Jun
    • Re: Distinct problemTanner Postert18 Jul
      • Re: Distinct problemTanner Postert18 Jul
        • Re: Distinct problemGerald L. Clark18 Jul