CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
ulquiorra
ulquiorra

Reputation: 945

selecting max values grouped by two column

I have a simple table like this

firstname, lastname , nb_payments
a, b , 10
a, b , 20
b, a , 30
b, a , 40
b, b , 50

I want to select the 3 max payments grouped by the couple firstname, lastname

The output desired is like this

firstname, lastname , top3
b, a , 70
b, b , 50
a, b , 30

I did this query : SELECT firstname, lastname , nb_payments as top3 FROM account ORDER BY nb_payments DESC LIMIT 3

But it doesn't add up nb_payments grouped by the couple firstname,lastname

Any help would be appreciated Thank you very much

Upvotes: 1

Views: 120

Answers (3)

shzad
shzad

Reputation: 36

This is how i can write in oracle, if it helps. there are other ways also.

select * from(
    SELECT firstname, lastname , sum(nb_payments) as top3 FROM test group by firstname,lastname ORDER BY top3 desc )
    where rownum <=3

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I think this produces the output you want:

select firstname, lastname, sum(nb_payments)
from (select t.*,
             row_number() over (partition by firstname, lastname
                                order by nb_payments desc) as seqnum
      from table t
     ) t
where seqnum <= 3
group by firstname, lastname;

Upvotes: 1

Mureinik
Mureinik

Reputation: 311018

You need to sum the payments and group by firstname and lastname

SELECT   firstname, lastname , SUM(nb_payments) 
FROM     account 
GROUP BY firstname, lastname
ORDER BY 3 DESC LIMIT 3

Upvotes: 1

Related Questions