DISTINCTQuery Techniques
Buy Me A Coffee

Eliminate duplicate rows in a result set.

Eliminates duplicate rows from the result set, forcing the database to sort or hash to find uniqueness. Often a symptom of missing JOIN conditions or imprecise filtering. Consider whether deduplication belongs in the query or the application layer.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all DISTINCT ON not supported
PostgreSQL ✓ Supported all Supports DISTINCT ON
SQL Server ✓ Supported all Standard only
Oracle ✓ Supported all Standard only
SQLite ✓ Supported all Standard only

Details

Remove duplicate rows.

Standard Syntax

SELECT DISTINCT column FROM tbl;

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

DISTINCT ON not supported

SELECT DISTINCT col FROM tbl;

PostgreSQL

Supports DISTINCT ON

SELECT DISTINCT ON (col1) col1, col2 FROM tbl;

SQL Server

Standard only

SELECT DISTINCT col FROM tbl;

Oracle

Standard only

SELECT DISTINCT col FROM tbl;

SQLite

Standard only

SELECT DISTINCT col FROM tbl;