PostgreSQL 10 High Performance

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 ...