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]



Reply via email to