Distinct mixing rows values?

Question

I'm getting the result with mixed dates values, instead get the last revision for each title i get them mixed.

I'm using MySQL.

The general idea is retireve all rows for each entry, the last revision of each entry.

My current sql query:

  SELECT DISTINCT 
         w.owner_id, 
         w.date, 
         w.title, 
         MAX(w.revision), 
         u.name AS updater 
    FROM wiki_pages AS w 
    JOIN users AS u ON w.owner_id = u.id 
GROUP BY title 
ORDER BY title ASC

SQL TABLE

Answer 1

Use:

SELECT wp.owner_id,
       wp.date,
       wp.title,
       wp.revision,
       u.name AS updater
  FROM WIKI_PAGES wp
  JOIN USERS u ON u.id = wp.owner_id
  JOIN (SELECT t.title,
               MAX(t.revision) AS max_rev
          FROM WIKI_PAGES t
      GROUP BY t.title) x ON x.title = wp.title
                         AND x.max_rev = wp.revision

In your query, the only thing you can guarantee is the title & the revision value is the highest. The other rows aren't necessarily related, hence the join to a derived table...