1

I have a table called documents that has the fields id, title, version and content.

Now I want to get all ids (or rows) for every title with the highest version.

Suppose I have the following data:

+----+-------+---------+---------+
| id | title | version | content |
+----+-------+---------+---------+
|  2 | foo   |       1 | abc1    |
|  3 | foo   |       2 | abc2    |
|  4 | foo   |       3 | abc3    |
|  5 | bar   |       1 | abcd1   |
|  6 | bar   |       2 | abcd2   |
|  7 | baz   |       1 | abcde1  |
+----+-------+---------+---------+

I want to receive either the ids 4,6,7 or the whole rows for these entries.

Performance is not an issue as there will be only a few hundred entries.

ayckoster
  • 6,707
  • 6
  • 32
  • 45
  • You can group them. A great example is here: http://stackoverflow.com/questions/3998529/selecting-top-n-rows-for-each-group-in-a-table – David Manheim Jun 21 '12 at 17:40

4 Answers4

4

To retrieve the entire rows, you need to GROUP BY version with a MAX() aggregate, and join that as a subquery against the whole table to pull in the remaining columns. The JOIN condition will need to be against the combination of title, version since together they uniquely identify a title's greatest version.

The following should work independently of the RDBMS you are using:

SELECT 
  documents.id,
  documents.title, 
  documents.version,
  documents.content
FROM 
  documents
  JOIN (
    /* subquery pulls greatest version per title */
    SELECT
      title,
      MAX(version) AS version
    FROM documents
    GROUP BY title
    /* title, version pair is joined back against the main table */
  ) maxversions ON (documents.title = maxversions.title AND documents.version = maxversions.version)
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
1

This is a simple group by:

select max(version)
from documents
group by title

You can join back to documents, if you like, to get the full document information.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

How about:

SELECT * FROM dataz
WHERE version IN (
  SELECT TOP 1 version
  FROM dataz
  ORDER BY version DESC
)
bluevector
  • 3,485
  • 1
  • 15
  • 18
0

This will get the IDs you want:

  Select max(id) as id from documents
group by title
  having count(1) = max(version)
order by id
Jcis
  • 153
  • 2
  • 15