CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
gabriele
gabriele

Reputation: 109

Highest per each group

It's hard to show my actual table and data here so I'll describe my problem with a sample table and data:

create table foo(id int,x_part int,y_part int,out_id int,out_idx text);

insert into foo values (1,2,3,55,'BAK'),(2,3,4,77,'ZAK'),(3,4,8,55,'RGT'),(9,10,15,77,'UIT'),
                       (3,4,8,11,'UTL'),(3,4,8,65,'MAQ'),(3,4,8,77,'YTU');

Following is the table foo:

id x_part y_part out_id out_idx 
-- ------ ------ ------ ------- 
3  4      8      11     UTL     
3  4      8      55     RGT     
1  2      3      55     BAK     
3  4      8      65     MAQ     
9  10     15     77     UIT     
2  3      4      77     ZAK     
3  4      8      77     YTU

I need to select all fields by sorting the highest id of each out_id.
Expected output:

id x_part y_part out_id out_idx 
-- ------ ------ ------ ------- 
3  4      8      11     UTL     
3  4      8      55     RGT     
3  4      8      65     MAQ     
9  10     15     77     UIT

Using PostgreSQL.

Upvotes: 4

Views: 164

Answers (3)

wildplasser
wildplasser

Reputation: 44250

Finding max(val) := finding the record for which no larger val exists:

SELECT * 
FROM foo f
WHERE NOT EXISTS (
   SELECT 317
   FROM foo nx
   WHERE nx.out_id = f.out_id
   AND nx.id > f.id
   );

Upvotes: 1

user330315
user330315

Reputation:

Postgres specific (and fastest) solution:

select distinct on (out_id) *
from foo
order by out_id, id desc;

Standard SQL solution using a window function (second fastest)

select id, x_part, y_part, out_id, out_idx
from (
  select id, x_part, y_part, out_id, out_idx, 
         row_number() over (partition by out_id order by id desc) as rn
  from foo
) t
where rn = 1
order by id;

Note that both solutions will only return each id once, even if there are multiple out_id values that are the same. If you want them all returned, use dense_rank() instead of row_number()

Upvotes: 4

Vivek S.
Vivek S.

Reputation: 21963

select * 
from foo 
where (id,out_id) in (
select max(id),out_id from foo group by out_id
) order by out_id

Upvotes: 1

Related Questions