0

I have a table that contains a column year. I need, for each row, calculate the number of entries where the year is less than that in the row.

So that the output would look like:

col1 | col2 | ... | year | number of entries prior to this year

I'm using PostgreSQL.

0

You can use the RANK() function for this:

SELECT 
    col1, col2, ..., year,
    RANK() OVER (ORDER BY year) - 1
      AS number_of_entries_prior_to_this_year
FROM
    tableX ;

Tested in SQLFiddle.

There is also a more complicated approach:

SELECT 
    col1, col2, year,
    COUNT(*) OVER (ORDER BY year) - COUNT(*) OVER (PARTITION BY year)
      AS number_of_entries_prior_to_this_year
FROM
    tableX ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.