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)