sql request : return the list of the first 5 best orders by summing them according to the client's name

Question

I am in the process of manipulating an SQL database. In fact, I need an SQL query that can return the list of the first 5 orders by summing them according to the name of the client.

To better explain: I have a table that contains a customer list and the price of each order according to the customer. At first, I want to sum up all the orders according to the customer and then extract the 5 best customers

The table :

price   client
  0,00  client1
200,00  client2
205,00  client5
312,00  client3
335,00  client2
502,00  client1
584,00  client5
602,00  client3
735,00  client1
335,00  client1
502,00  client4
584,00  client7
602,00  client8
735,00  client8
584,00  client9
602,00  client3
735,00  client1
335,00  client6
502,00  client4

Any idea, please?

Answer 1

Use Dense_Rank in selecting Top N rows like this:

SELECT Client, Price 
FROM (
      SELECT client, 
             price, 
             DENSE_RANK() OVER(ORDER BY Price DESC) AS rank 
     FROM (
           SELECT client,
                  SUM(price) AS Price
           FROM Table1
           GROUP BY client
          ) AS t1
    ) AS t2
WHERE rank <= 5

OUTPUT:

client   price
----------------
client1  2307.00
client3  1516.00
client8  1337.00
client4  1004.00
client5  789.00

Link To the demo:

https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=ae7e8d09403d999ed645ebde33eeedde

Why to use Dense_Rank()?

Take a case if 6 clients having the same total price. So in that case, LIMIT or Fetch First will not work since you will not get all 6 clients while all 6 are best customers.