CODEWITHSUNDEEP
sqlpostgresql
jorge martinez
jorge martinez

Reputation: 13

Get ID of row from max value

I'm new in database

I have the following table:

id  group       factor
------------------------
1   11          1
2   11          5
4   11          2
5   12          3
6   12          2
7   13          4
8   13          1
9   13          8
10  14          6

I need to get the id of the one that has the largest factor based on its group, for example, in the case of group 11, the row with the largest factor is 5, so I need to obtain the id of that row, in this case 2.

please if someone can show me the right way.

Upvotes: 1

Views: 57

Answers (3)

Martin Bouladour
Martin Bouladour

Reputation: 359

If you know the group in advance, e.g. 11, then you can simply do:

SELECT id
FROM tab
WHERE group = 11
ORDER BY factor DESC
LIMIT 1;

Otherwise if you want the result for each group that exists in the table then Lukasz Szozda's answer it the way to go.

Upvotes: 0

The Impaler
The Impaler

Reputation: 48770

You can use simple CTE (Common Table Expression) for it, as in:

with
x as (
  select group_id, max(factor) as max_factor from my_table group by group_id
)
select t.*
from my_table t
join x on x.group_id = t.group_id and x.max_factor = t.factor

This solution has the [desirable?] feature that in case there are multiple rows tied in first place in the same group, it will show them all, not just one [randomly] of them.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You could use:

SELECT DISTINCT ON(group) group factor, id
FROM tab
ORDER BY group, factor DESC;

db<>fiddle demo

Upvotes: 2

Related Questions