How Can I Add Row Numbers to My PostgreSQL Query Results?

Adding Row Numbers to PostgreSQL Query Results

Displaying row numbers can provide valuable context to your PostgreSQL query results. PostgreSQL introduced the ROW_NUMBER() window function in version 8.4, which allows you to add row numbers to your queries.

Syntax:

The syntax for ROW_NUMBER() is as follows:

ROW_NUMBER() OVER (PARTITION BY partition_expression ORDER BY sort_expression)

• partition_expression: Optional. Groups rows together before numbering.
• sort_expression: Optional. Determines the order in which row numbers are assigned.

Example:

To assign row numbers without sorting or grouping, use:

SELECT ROW_NUMBER() OVER () AS rownum, *
FROM foo_tbl;

Sorting and Grouping:

To sort and group rows before assigning row numbers, specify the ORDER BY and PARTITION BY clauses, respectively:

SELECT ROW_NUMBER() OVER (ORDER BY last_name) AS rownum, *
FROM employee_tbl;

This query will assign row numbers based on the values in the last_name column, with ties resolved by the default sorting order of the table.

Simplifying the Query:

If sorting or grouping is not required, you can simplify the query as follows:

SELECT ROW_NUMBER() OVER () AS rownum, *  -- no fields in the ORDER BY clause
FROM foo_tbl;

SQL Fiddle Example:

A working example can be found at [SQL Fiddle](https://sqlfiddle.com/#!18/6e750c/1).

The above is the detailed content of How Can I Add Row Numbers to My PostgreSQL Query Results?. For more information, please follow other related articles on the PHP Chinese website!