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