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

Reply via email to