Rank query using row_number reversed

Question

I have a query where I am ranking the returned rows (related question). What I would like to be able to do is reverse the rank, and set the new_rank accordingly. Lets assume I have 999 entries.

For instance if I have this query:

with persondata as (
   SELECT firstname,
          lastname,
          year,
          personid,
        ((SELECT COALESCE(SUM(thevalue),0)
          FROM assets
          WHERE personidref = w.personid AND year = w.year)
       - (SELECT COALESCE(SUM(amount),0)
          FROM liabilities
          WHERE personidref = w.personid AND year = w.year)) as worth,
          row_number() over(ORDER BY w.worth DESC) as new_rank,
         visible
   FROM members w 
   WHERE year = 2014 
   AND visible = 1 
   ORDER BY worth ASC LIMIT 5
) 
select row_number() over (order by worth DESC) as rank,
       * 
from persondata

This rightly returns the bottom 5 in the desired order see below:

 rank | firstname |     lastname      |  worth   | new_rank
------+-----------+-------------------+----------+----------
    1 | Peter     | Griffin           |  -520000 |      145 
    2 | Steve     | Moffat            |  -530000 |      519 
    3 | Gregory   | Peck              |  -540000 |      131 
    4 | Ruben     | Mumbles           |  -550000 |      130 
    5 | Ricky     | Swiss             |  -560000 |      120

Is there a way to incorporate the row_count and subtract current row count? Or set a counter to be used temporarily, that is set to row count then decrement?

Final desired result:

 rank | firstname |     lastname      |  worth   | new_rank 
------+-----------+-------------------+----------+----------
  997 | Peter     | Griffin           |  -520000 |      145 
  996 | Steve     | Moffat            |  -530000 |      519 
  997 | Gregory   | Peck              |  -540000 |      131 
  998 | Ruben     | Mumbles           |  -550000 |      130 
  999 | Ricky     | Swiss             |  -560000 |      120

I haven't been able to find anything regarding this implementation on using row_number.

try `count(*) over () - row_number() over(ORDER BY w.worth DESC) as new_rank` — , Oct 21 '14 at 17:34
ah yes, but if the query is `LIMIT 5` it counts the rank down as 4,3,2,1,0. As opposed to (*) being the full number of queries 999 resulting in 999,998, etc. — roberthuttinger, Oct 21 '14 at 19:29
you can move the `count(*) over ()` into the cte to get the full count. — , Oct 21 '14 at 19:31
the `with` part: http://www.postgresql.org/docs/current/static/queries-with.html — , Oct 21 '14 at 20:34

score 2 · Answer 1 · edited May 23 '17 at 12:32

Problems

No wonder you got stuck with this query. Your formatting and naming conventions are means of obfuscation (format has been edited by now).

It is very confusing that you compute new_rank based on members.worth, then compute a new worth (re-using the same name!) and a rank based on that.
- Don't reuse the same name "worth". Use separate, meaningful identifiers.
- Why would you call the rank based on the old value "new_rank"?
Why would you compute a "rank" with row_number() instead of rank() to begin with?
Why would you use an alias w for a table members?
Your ORDER BY worth uses the newly computed worth, not members.worth. Are you aware of that? It is very unwise to build traps like that, unless you actually want to obfuscate your code.
If you want both rankings, you cannot LIMIT 5 in the CTE (Common Table Expression). To get the relative standing, you have to compute the new worth for all relevant rows. However, it's unclear which rows are actually relevant for your ranking. All rows in members? Just the ones passing your WHERE conditions?
Finally, it is undefined how to break ties. What do you expect as result if the 3rd to the 7th highest calculated worth are identical?

Solution

Consider the sequence of events in a SELECT query:

How will the order of operations in this query affect my results?

I wouldn't use CTEs at all here. It's not needed and probably slower than a subqueries. Aggregate the sum of assets and liabilities per person before joining. this way, you avoid multiplying rows:

Two SQL LEFT JOINS produce incorrect result

I use calc_worth and calc_rank instead of your output columns worth and rank to untangle the naming a bit.

SELECT count(*) OVER ()     -- AS total_ct
       + 1                  -- counter off-by-1 error
       - rank() OVER (ORDER BY COALESCE(a.asset_sum, 0)
                             - COALESCE(l.liab_sum, 0)) AS calc_rank
     , m.firstname, m.lastname
     , COALESCE(a.asset_sum, 0) - COALESCE(l.liab_sum, 0) AS calc_worth
     , rank() OVER (ORDER BY m.worth DESC) AS rank
FROM  members m
LEFT  JOIN (
   SELECT personidref, sum(thevalue) AS asset_sum
   FROM   assets
   WHERE  year = 2014
   GROUP  BY 1
   ) a ON a.personidref = m.personid
LEFT JOIN (
   SELECT personidref, sum(amount) AS liab_sum
   FROM   liabilities
   WHERE  year = 2014
   ) l ON l.personidref = m.personid
WHERE  m.year = 2014 
AND    m.visible = 1 
ORDER  BY calc_worth DESC
LIMIT  5;

If you want to reverse the sort order in the result, wrap this in a subquery and order once more in the outer query.

Rank query using row_number reversed

1 Answers1

Problems

Solution