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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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
