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] > >
