0

I have the following data:

account_id note_id size     id
---------- ------- ------ --------
48         1554    196036 28229509
48         1554    18777  28229588
48         1554    4861   28229566
48         1559    176406 28229516
48         1559    82041  28229521
48         1559    3063   28229541
48         1583    92150  28229514
48         1583    3495   28229501
48         1583    119203 28229534
8764       25556838 5126   28229567
8764       25556838 340618 28229508

I want to get the id of the highest value on the size column, for each note_id.

Example:

note_id: 25556838
size: 340618
id: 28229508

note_id: 1583
size: 119203
id: 28229534

How can I get that data?

Patrick B.
  • 191
  • 4
  • 12

1 Answers1

1

Queries for can be solved using window functions:

select note_id, size, id
from (
   select note_id, size, id, 
          row_number() over (partition by note_id order by size desc) as rn
   from the_table
) t
where rn = 1;

The above is standard SQL, but Postgres as another (proprietary) feature that can be used for this:

select distinct on (note_id) note_id, size, id
from the_table
order by node_id, size desc;

The distinct on () solution is usually faster then the solution using window functions.