ROFLMAO -- Yep! It's been one of _those_ days!!! Shawn
Michael Stassen <[EMAIL PROTECTED]> wrote on 08/17/2004 05:10:46 PM: > Just a minor quibble: You don't need DISTINCT here, because the GROUP BY > will ensure distinct results. Probably just another fast-typing symptom. > > Michael > > [EMAIL PROTECTED] wrote: > > > oops! - typing too fast for my own good. The fixed query should have been: > > > > SELECT DISTINCT E.Title, E.ID, max(C.DateAdded) as maxdate > > FROM Entries E, Comments C > > WHERE C.EntryID = E.ID > > GROUP BY E.Title, E.ID > > ORDER BY maxdate desc > > LIMIT 10 > > > > 8-D > > Shawn > > > > [EMAIL PROTECTED] wrote on 08/17/2004 03:26:53 PM: > > > > > >>I don't think the engine is messing up here. I think you are getting > >>exactly what you asked for and I will explain why I think that. > >> > >>You are asking for 3 things from 2 tables: > >> Entries.ID > >> , Entries.Title > >> , Comments.DateAdded. > >> > >>If Entry #1 was called "My First Thread" and had 3 comments associated > >>with it dated '2004-07-04 08:00:00', '2004-07-04 08:05:00', and > >>'2004-07-04 08:10:00', you would get these three records back: > >> > >>(1, 'My First Thread', '2004-07-04 08:00:00') > >>,(1, 'My First Thread', '2004-07-04 08:05:00') > >>,(1, 'My First Thread', '2004-07-04 08:10:00') > >> > >>Why? because the dates are different.... the DISTINCT modifier looks at > >>ALL of the columns in the SELECT statement not just the first one or > > > > two. > > > >>So if there were a second Comment for the same entry that just happened > > > > to > > > >>have the exact same time '2004-07-04 08:00:00', the DISTINCT modifier > >>would eliminate that duplicate entry and you would still only get 3 > >>records back. > >> > >>What you wanted for a statement was: > >> > >>SELECT DISTINCT E.Title, E.ID, max(C.DateAdded) as maxdate > >>FROM Entries E, Comments C > >>WHERE C.EntryID = E.ID > >>GROUP BY E.Title, E.ID > >>ORDER BY C.DateAdded desc > >>LIMIT 10 > >> > >>This will show you the ten most recently updated Entries and the date of > > > > > >>their last update. > >> > >>Yours, > >>Shawn Green > >>Database Administrator > >>Unimin Corporation - Spruce Pine > >> > >> > >>Ron Gilbert <[EMAIL PROTECTED]> wrote on 08/17/2004 03:02:43 PM: > >> > >> > >>>I am trying to run this query... > >>> > >>>select distinct E.Title, E.ID, C.DateAdded from > >>>Entries E, Comments C > >>>where C.EntryID = E.ID > >>>order by C.DateAdded desc > >>>limit 10 > >>> > >>>...to get the last 10 entries that had comments added to them, but the > > > > > >>>DISTINCT is not returning distinct results. What I get is 10 entries > >>>that had the last 10 comments, but there are duplicates. For example, > > > > > >>>if entry "A" just had 3 comments added, it gets return as the first 3 > >>>results, not just once. I assume the DISTINCT is failing because of > >>>the ORDER. How should this be accomplished? I am running 3.23 > >>> > >>>Thanks, Ron > >>> > >>> > >>>-- > >>>MySQL General Mailing List > >>>For list archives: http://lists.mysql.com/mysql > >>>To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >>> > > > > >
