MySQL: How do I use distinct on a column, and yet still select all the columns?

This yields a syntax error:

SELECT id, name, body, DISTINCT(sequence) FROM `contents` 
WHERE (`开发者_如何学运维contents`.section_id = 74) 
ORDER BY sequence ASC, revision DESC

Hokay, so, In my table, I want to select the record with the most recent revision number based on the sequence number.

So... I can have records like this

seq | rev
0 | 2
0 | 1
0 | 0
1 | 1
1 | 0
2 | 3
2 | 1
2 | 0

and I'm trying to write a query to return the following

   seq | rev
    0 | 2
    1 | 1
    2 | 3

note: that I left out all the other columns for the sake of cleanliness of the question.

note2: exactly, (sequence, revision) combos are always unique.

---

This query selects all records where the revision matches the maximum revision for the same sequence, and returns the other fields for the same record too.

SELECT c.id, c.name, c.body, c.sequence 
FROM contents c
WHERE 
  c.section_id = 74 AND
  IFNULL(c.revision, -1) = 
    (SELECT
      IFNULL(MAX(cx.revision), -1)
    FROM
      contents cx 
    WHERE
      cx.section_id = 74 AND /* Added this */
      cx.sequence = c.sequence)
ORDER BY sequence

---

The subquery:

SELECT sequence, max(revision) as maxrev FROM contents 
WHERE section_id = 74
GROUP BY sequence

will give you a result with the (sequence, revision) pairs that pick out the records you want (as you said a pair of those values is unique in the table). Use an inner join to the original table to pick out the other columns:

SELECT c.id, c.name, c.body, c.sequence 
FROM contents as c INNER JOIN 
    (SELECT sequence, max(revision) as maxrev FROM contents
     WHERE section_id = 74 GROUP BY sequence) as p 
    ON (c.sequence = p.sequence AND c.revision = p.maxrev)
WHERE c.section_id = 74
ORDER BY c.sequence

Depending on whether the (sequence, revision) pairs are unique across the entire table, or only in a set of records with the same section_id, the WHERE clause in the outer query may be redundant.

---

Does GROUP BY and MAX() not work in your case?

SELECT 
id,
name, 
body, 
sequence as seq,
MAX(rev) as m_rev
FROM 
`contents` 
WHERE (`contents`.section_id = 74) 
GROUP BY sequence 
ORDER BY sequence ASC, rev DESC

---

May be something like this could be used :

SELECT id, name, body, sequence FROM contents c 
WHERE section_id = 74 AND revision in ( SELECT MAX(revision) FROM contents WHERE 
sequence=c.sequence) ORDER BY sequence ASC