List:General Discussion« Previous MessageNext Message »
From:Dan Buettner Date:June 28 2006 9:55pm
Subject:Re: Distinct problem
View as plain text  
Use the MAX() function, like so:

select text, MAX(dt) as dt, item_id from table
where ....
group by item_id
order by dt DESC

Dan


On 6/28/06, Tanner Postert <tanner.postert@stripped> 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
>
>
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