CODEWITHSUNDEEP
sqlpostgresql
Александр Коромыслов
Александр Коромыслов

Reputation: 5

Select all unique values of all attributes in one query

I have a table and I want to select all unique values of all attributes in one query.

For example table Person with 3 columns name, age, city.

Example:

Name age city
Alex 34 New York
Leo 34 London
Roy 20 London
Alex 28 Moscow
Mike 36 London

And I want to have a result with unique values of every attribute

Name age city
Alex 20 New York
Leo 28 London
Roy 34 Moscow
36

Is it possible to do this query?

I tried to make some queries with DISTINCT and UNION, but the result with always a multiplication of rows.

Upvotes: 0

Views: 324

Answers (2)

user330315
user330315

Reputation:

One option is to aggregate into array, then unnest those arrays:

select x.*
from (
  select array_agg(distinct name) as names, 
         array_agg(distinct age) as ages,
         array_agg(distinct city) as cities
  from the_table
) d
  cross join lateral unnest(d.names, d.ages, d.cities) with ordinality as x(name, age, city);

I would expect this to be quite slow if you really have many distinct values ("millions"), but if you only expect very few distinct values ("hundreds" or "thousands") , then this might be OK.

Upvotes: 1

The Impaler
The Impaler

Reputation: 48750

This is not how relational databases work, but sometimes you got to do what you got to do.

You can do:

select a.name, b.age, c.city
from (select distinct name, row_number() over() as rn from t) a
full join (select distinct age, row_number() over() as rn from t) b on b.rn = a.rn
full join (select distinct city, row_number() over() as rn from t) c
  on c.rn = coalesce(a.rn, b.rn)

Upvotes: 1

Related Questions