Query for highest version

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.

greatest-n-per-group 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;

StackExchange Database Administrators Q#154595, answer score: 8