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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
