Postgresql – Calling SELECT DISTINCT on multiple columns

distinctpostgresql

I am unclear on exactly on,

SELECT DISTINCT col1, col2, ...
FROM table_name

When called on one column it gives back only distinct values of that column. What happens when we use it with multiple columns and should we ever do this?

Best Answer

How does this work exactly?

It gives you distinct combinations of all the expression in the SELECT list.

SELECT DISTINCT col1, col2, ... 
FROM table_name ;

is also equivalent to:

SELECT col1, col2, ... 
FROM table_name 
GROUP BY  col1, col2, ... ;

Another way to look at how it works - probably more accurate - is that it acts as the common bare SELECT (ALL) and then removes any duplicate rows. See Postgres documentation about SELECT: DISTINCT clause.

Should we ever do this?

Of course. If you need it, you can use it.

Related Solutions

Select only those records that have different/multiple values for a particular column

You could do something like:

select distinct x.id, x.lastname, x.firstname, x.email
from t as x
join (
    select id
    from t
    group by id
    having count(distinct email) > 1
) as y
    on x.id = y.Id

Mysql – Distinct Combination of Two Columns

You can count distinct elements by running:

select count(distinct policy_id, client_id) from policy_client;

Another option would be to group by and count that:

select count(*) from (select policy_id, client_id from policy_client group by 1,2) a;

Run both version and see which one performs better on your dataset.

A very quick way but not totally accurate if you have a key on (policy_id and client_id) you can also check the cardinality of that index but that's an approximate not exact number.

Best Answer

Related Solutions

Select only those records that have different/multiple values for a particular column

Mysql – Distinct Combination of Two Columns

Related Question