ROW_NUMBER in PostgreSQL

ROW_NUMBER is a window function that assigns an unique integer value (which starts with one and increments by one) to each row in a result set.

ROW_NUMBER() OVER(
    [PARTITION BY column_1, column_2,…]
    [ORDER BY column_3,column_4,…]
)

ROW_NUMBER() operates on a set of rows called a window. PARTITION BY clause splits this window into smaller subsets (i.e. partitions); if omitted, ROW_NUMBER() will treat the entire window as a single partition. ORDER BY inside OVER determines the order in which the numbers are assigned.

While using DISTINCT in conjugtion with ROW_NUMBER, it's important to remember that ROW_NUMBER() operates on the result set before the DISTINCT is applied, i.e. instead of this query

SELECT DISTINCT salary, ROW_NUMBER () OVER (ORDER BY salary)
FROM employees ORDER BY salary;

use this query

WITH salaries AS (
  SELECT DISTINCT salary FROM employees
) SELECT salary, ROW_NUMBER () OVER (ORDER BY salary)
FROM salaries;

ROW_NUMBER() can be used for pagination.

SELECT * FROM (
  SELECT *, ROW_NUMBER () OVER (ORDER BY name)
  FROM employees
) x WHERE ROW_NUMBER BETWEEN 7 AND 13;