Re: DISTINCT and ORDER (not a new NBC TV show)

[Michael Stassen]

Ron Gilbert wrote:
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 is doing what you asked, rather than what you want.  That is, it is 
returning distinct results.  You are ignoring the different values for 
DateAdded, but MySQL is not.  So, as you say, you are getting the last 10 
updates, period.

I believe
  SELECT E.title, E.id, MAX(C.DateAdded) AS last_update
  FROM Entries E, Comments C
  WHERE C.EntryID = E.ID
  GROUP BY E.title, E.id
  ORDER BY last_update DESC
  LIMIT 10;
would do what you want.
Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]