SQL Server and PostgreSQL – Query for Highest Version

greatest-n-per-grouppostgresqlsql server

I have the following table (stripped to the bare essentials):
(script is in postgresql)

CREATE TABLE t_version (
  name VARCHAR(64) NOT NULL,
  major INTEGER NOT NULL,
  minor INTEGER NOT NULL,
  attr VARCHAR(64)
);

ALTER TABLE t_version
  ADD CONSTRAINT PK_VERSION
  PRIMARY KEY (name, major, minor);

And some test data:

INSERT INTO t_version (name, major, minor, attr) VALUES ('n1', 1, 0, 'a1');
INSERT INTO t_version (name, major, minor, attr) VALUES ('n1', 1, 1, 'a2');
INSERT INTO t_version (name, major, minor, attr) VALUES ('n1', 2, 0, 'a3');

I am trying to figure out, how to select the highest version from this, i.e. a select which would give me

'n1', 2, 0, 'a3'

as a result.

I know how to do that, when only the highest minor version is required:

SELECT A.name, A.major, A.minor, A.attr
  FROM t_version AS A
    JOIN (
           SELECT name, major, 
             max(minor) AS minor
           FROM t_version
           GROUP BY name, major
         ) LATEST
      ON A.name = LATEST.name
         AND A.major = LATEST.major
         AND A.minor = LATEST.minor;

But I am at a loss for the more general problem.

BTW, the solution should not only work for postgresql by for sql server too.

Best Answer

problems are usually solved using window functions.

The following is standard SQL and will work on SQL Server as well:

select name, major, minor, attr
from (
  select name, major, minor, attr, 
         row_number() over (partition by name order by major desc, minor desc) as rn
  from t_version
) t
where rn = 1
order by name;

For Postgres it's typically faster to use distinct on () instead:

select distinct on (name) name, major, minor, attr
from t_version
order by name, major desc, minor desc;
Related Question