Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

ROW_NUMBER ​

Generate a sequence of row numbers based on an ordered set of rows.

ROW_NUMBER() OVER { window_name | ( window_definition ) }

The ORDER BY clause in the window_definition is required. The frame clause, if specified, is ignored. For the full window_definition syntax, see Syntax for Window Functions.

For example:

premdb=# SELECT ROW_NUMBER() OVER(ORDER BY name NULLS LAST), 
name FROM team ORDER BY name;
 row_number |          name           
------------+-------------------------
         1 | Arsenal
         2 | Aston Villa
         3 | Barnsley
         4 | Birmingham City
         5 | Blackburn Rovers
         6 | Blackpool
         7 | Bolton Wanderers
         8 | Bournemouth
         9 | Bradford City
        10 | Burnley
...

The following example partitions the results by city and constrains the result to London.

premdb=# SELECT ROW_NUMBER() OVER(PARTITION BY city ORDER BY city NULLS LAST), 
city, name FROM team WHERE city='London' ORDER BY city;
 row_number |  city  |        name         
------------+--------+---------------------
         1 | London | Arsenal
         2 | London | Charlton Athletic
         3 | London | Chelsea
         4 | London | Crystal Palace
         5 | London | Fulham
         6 | London | Queens Park Rangers
         7 | London | Tottenham Hotspur
         8 | London | West Ham United
         9 | London | Wimbledon
(9 rows)

Parent topic:Window Functions