PostgreSQL 10 High Performance - Third Edition

Using Window functions for numbering

PostgreSQL 8.4 introduces support to handle this sort of problem using features added to newer SQL standards. These add the concept of a window over which you can get additional data. For example, you can make your window in this type of query to line up with each customer, then use the row_number() function to find out the ranking:

SELECT row_number() OVER (ORDER BY sum(netamount) DESC) AS rank,customerid,sum(netamount) as sales FROM orders GROUP BY customerid ORDER BY sales DESC LIMIT 5;
     rank | customerid |  sales  
    ------+------------+---------
        1 |      15483 | 1533.76
        2 |       9315 | 1419.19
        3 |      15463 | 1274.29
        4 |      10899 | 1243.14
        5 |       3929 | 1196.87
  

That's not only a whole lot cleaner, it's way more efficient ...