Postgres: How to get row number when ordered by the result of operation?

Question

Let us assume there is a table ‘items’ that has three columns: ‘id’, ‘pos’ and ‘neg’, and the result of selection should be ordered by the result of operation pos - neg.

So, the following is supposed to work:

SELECT
    id,
    pos - neg AS diff
FROM items
ORDER BY diff DESC

Now I need to get position of a specific row (in table) ordering the result by 'diff'. I tried this:

WITH summary AS (
    SELECT
        i.id,
        i.pos - i.neg AS diff,
        ROW_NUMBER() OVER(ORDER BY diff) AS position
    FROM items i)
SELECT s.*  FROM summary s WHERE s.id = 351435254

but the execution returns ERROR: column "diff" does not exist.

So, is it possible to get the position, or it would be better to keep diffs in a separate column?

Answer 1

Try:

WITH summary AS (
    SELECT
        i.id,
        i.pos - i.neg AS diff,
        ROW_NUMBER() OVER(ORDER BY (i.pos - i.neg)) AS position
    FROM items i)
SELECT s.*  FROM summary s WHERE s.id = 351435254