CODEWITHSUNDEEP
sqlpostgresqlgroup-by
Audrius Meškauskas
Audrius Meškauskas

Reputation: 21728

Can I fetch the id of column having max value on some other field?

I need to find a row in the table where the value of some column is the largest, grouping by another column. I need more columns in that row, not just the value itself (say I need id). Finding the value itself is trivial:

select max(v), g from t group by g

but this does not tell me for row does the value applies. For sure I could now run

select id from t where g='a' and v=0.1234
select id from t where g='b' and v=0.1233

and so on

but then I have n+1 queries where I was expecting to have one only, and I have doubts if direct comparison of the floating point values would not result the rounding errors. If already to compare the floating point values, maybe better at least without passing them over JDBC both directions.

What is the best way to achieve this with standard SQL, or with PostgreSQL? I am aware about stored procedures but would like to avoid them if possible.

The minimal example of the table would be:

create table t (
    id int,
    g varchar,
    v float
);
insert into t (id, g, v) values (1, 'a', 0.1234);
insert into t (id, g, v) values (2, 'a', 0.1235);
insert into t (id, g, v) values (3, 'b', 0.1231);
insert into t (id, g, v) values (4, 'b', 0.1234);

The original real-world table is too large and complex for the minimal example.

Upvotes: 1

Views: 529

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

A typical solution is to use row_number():

select t.*
from (select t.*,
             row_number() over (partition by g order by v desc) as seqnum
      from t
     ) t
where seqnum = 1;

In many databases, a correlated subquery is faster:

select t.*
from t
where t.v = (select max(t2.v) from t t2 where t2.g = t.g);

These are slightly different, because this will return duplicates, if the maximum value is duplicated for some g.

Some databases have other mechanisms as well. In Postgres, the typical solution would use distinct on:

select distinct on (g) t.*
from t
order by g, val desc;

This usually has the best performance.

Upvotes: 3

jarlh
jarlh

Reputation: 44746

JOIN with a subquery that returns each g with its max v value:

select t1.*
from t t1
join (select max(v) maxv, g from t group by g) t2
    on t1.g = t2.g and t1.v = t2.maxv

Upvotes: 1

Related Questions