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.