DISTINCT ON

DISTINCT ON is a SQL statement in the PostgreSQL Specific category. PostgreSQL extension: returns only the first row for each unique value of the expression. Faster than window functions. The syntax is SELECT DISTINCT ON (expression) column1, column2 FROM table ORDER BY expression, other_columns;. It returns first row per group. A typical example: SELECT DISTINCT ON (department) name, department, salary FROM employees ORDER BY department, salary DESC; -- Highest paid employee in each department SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, created_at DESC; -- Most recent order for each customer A close relative is SERIAL, which postgreSQL auto-increment type. Creates an INTEGER column with a sequence. Equivalent to AUTO_INCREMENT in MySQL. A close relative is RETURNING, which postgreSQL extension that returns values from INSERT/UPDATE/DELETE statements (like OUTPUT in SQL Server). A close relative is ARRAY, which postgreSQL supports array columns and array operations. Can be multi-dimensional.