CODEWITHSUNDEEP
mysql
NullVoxPopuli
NullVoxPopuli

Reputation: 65173

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.

Upvotes: 0

Views: 196

Answers (4)

UltraOne
UltraOne

Reputation: 166

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.

Upvotes: 1

etipici
etipici

Reputation: 415

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

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116140

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

Upvotes: 3

iHaveacomputer
iHaveacomputer

Reputation: 1437

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

Upvotes: 0

Related Questions