0

I need to group by a certain column and to make the first occurrence into another group.

select * from table;
id currency amount
1 USD 1
2 USD 2
3 EUR 3
4 IL 4
5 EUR 5
6 USD 6

So something like that:

select min(id) id, currency, sum(amount)
from table
group by currency, 
         case when min(id)=id then 'first-occurrence' else 'rest' end;
id currency amount
1 USD 1
2 USD 8
3 EUR 3
4 IL 4
5 EUR 5

Of course the case doesn't make much sense. The goal is make it also fast, and if possible not to read the table again as the table is huge.

1 Answer 1

1

RANK helped me:

select min(id) id, currency, sum(amount)
FROM (select id, currency, amount, 
              RANK () OVER
                   ( PARTITION BY currency
                     ORDER BY id 
                   ) a_rank
      from table)
group by currency, 
         case when a_rank=1 then 'first-occurrence' 
                            else 'rest' 
         end;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.