How to select single row based on the max value in multiple rows

Question

Possible Duplicate:
SQL: Find the max record per group

I have a table with four columns as such:

name   major    minor  revision
p1     0        4      3
p1     1        0      0
p1     1        1      4
p2     1        1      1
p2     2        5      0
p3     3        4      4

This is basically ca table containing records for each version of a program. I want to do a select to get all of the programs and their latest version so the results would look like this:

name   major    minor  revision
p1     1        1      4
p2     2        5      0
p3     3        4      4

I can't just group by the name and get the max of each column because then i would just end up with the highest number from each column, but not the specific row with the highest version. How can I set this up?

Answer 1

You can use a not exists subquery to filter out older records:

select  *
from    YourTable yt
where   not exists
        (
        select  *
        from    YourTable older
        where   yt.name = older.name and 
                (
                    yt.major < older.major or
                    yt.major = older.major and yt.minor < older.minor or
                    yt.major = older.major and yt.minor = older.minor and
                        yt.revision < older.revision
                )
        )

which can also be written in MySQL as:

select  *
from    YourTable yt
where   not exists
        (
        select  *
        from    YourTable older
        where   yt.name = older.name and 
                  (yt.major,    yt.minor,    yt.revision) 
                < (older.major, older.major, older.revision)
        )