# Postgresql – For each record, count records dated earlier in PostgreSQL

countpostgresql

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.

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 ;