List:General Discussion« Previous MessageNext Message »
From:Tanner Postert Date:July 18 2006 6:53pm
Subject:Re: Distinct problem
View as plain text  
I actually solved my own problem...

SELECT t1.item_id, t1.dt, t1.text ,t3.*
FROM table AS t1, table3 as t3
LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt < t2.dt
WHERE t2.item_id IS NULL;

becomes

SELECT t1.item_id, t1.dt, t1.text
FROM (table AS t1, table3 as t3)
LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt < t2.dt
WHERE t2.item_id IS NULL;

amazing what a little set of parenthesis will do. thanks anyways.





On 7/18/06, Tanner Postert <tanner.postert@stripped> wrote:
>
> 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