3

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.

8

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;
1
  • Thanks, works like a charm. And I have learned several new things ;-) – hdc Nov 8 '16 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.