Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgres: Distinct but only for one column

People also ask

How do I get distinct to only one column?

Adding the DISTINCT keyword to a SELECT query causes it to return only unique values for the specified column list so that duplicate rows are removed from the result set.

Does distinct work on multiple columns?

Answer. Yes, the DISTINCT clause can be applied to any valid SELECT query. It is important to note that DISTINCT will filter out all rows that are not unique in terms of all selected columns.

How do I get unique column values in PostgreSQL?

Syntax: SELECT DISTINCT ON (column_1) column_alias, column_2 FROM table_name ORDER BY column_1, column_2; As the order of rows returned from the SELECT statement is unpredictable which means the “first row” of each group of the duplicate is also unpredictable.

Does distinct apply to all columns?

Yes, DISTINCT works on all combinations of column values for all columns in the SELECT clause.


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.


Anyone knows how to fetch many columns but do only a distinct on one column?

You want the DISTINCT ON clause.

You didn't provide sample data or a complete query so I don't have anything to show you. You want to write something like:

SELECT DISTINCT ON (name) fields, id, name, metadata FROM the_table;

This will return an unpredictable (but not "random") set of rows. If you want to make it predictable add an ORDER BY per Clodaldo's answer. If you want to make it truly random, you'll want to ORDER BY random().


SELECT NAME,MAX(ID) as ID,MAX(METADATA) as METADATA 
from SOMETABLE
GROUP BY NAME

To do a distinct on n columns:

select distinct on (col1, col2) col1, col2, col3, col4 from names