Postgres: Distinct but only for one column

by

To do a distinct on only one (or n) column(s):

select distinct on (name)
    name, col1, col2
from names

This will return any of the rows containing the name. If you want to control which of the rows will be returned you need to order:

select distinct on (name)
    name, col1, col2
from names
order by name, col1

Will return the first row when ordered by col1.

distinct on:

SELECT DISTINCT ON ( expression [, …] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the “first row” of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.

The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group.

More Related Contents:

  1. Converting SELECT DISTINCT ON queries from Postgresql to MySQL
  2. invalid byte sequence for encoding “UTF8”
  3. How to make “case-insensitive” query in Postgresql?
  4. Rownum in postgresql
  5. Postgresql: Scripting psql execution with password
  6. dynamic sql query in postgres
  7. Cannot create a database table named ‘user’ in PostgreSQL
  8. Is there a timeout for idle PostgreSQL connections?
  9. How to make connection to Postgres via Node.js
  10. Postgresql Truncation speed
  11. Buffers (Circle) in PostGIS
  12. docker postgres pgadmin local connection
  13. SELECT raises exception in PL/pgSQL function
  14. Heroku “psql: FATAL: remaining connection slots are reserved for non-replication superuser connections”
  15. Changing primary key int type to serial
  16. Return SETOF rows from PostgreSQL function
  17. PSQLException: current transaction is aborted, commands ignored until end of transaction block
  18. How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?
  19. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  20. how to store PostgreSQL jsonb using SpringBoot + JPA?
  21. PostgreSQL how to concat interval value ‘2 days’
  22. Select distinct from multiple fields using sql
  23. Displaying image in Ireports using PostgreSql
  24. Orbeon Forms Postgres DB connection
  25. PostgreSQL disable more output
  26. PostgreSQL array_agg order
  27. Postgres table column name restrictions?
  28. PostgreSQL jsonb, `?` and JDBC
  29. Syntax error at end of input in PostgreSQL
  30. Error when pushing data to Heroku: time zone displacement out of range

Leave a Comment