List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:June 28 2006 10:02pm
Subject:Re: Distinct problem
View as plain text  
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
>   

Attachment: [text/html]
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