Selecting Distinct Values for Multiple Columns

Select distinct values from multiple columns in same table

It's better to include code in your question, rather than ambiguous text data, so that we are all working with the same data. Here is the sample schema and data I have assumed:

CREATE TABLE tbl_data (
  id INT NOT NULL,
  code_1 CHAR(2),
  code_2 CHAR(2)
);

INSERT INTO tbl_data (
  id,
  code_1,
  code_2
)
VALUES
  (1, 'AB', 'BC'),
  (2, 'BC', NULL),
  (3, 'DE', 'EF'),
  (4, NULL, 'BC');

As Blorgbeard commented, the DISTINCT clause in your solution is unnecessary because the UNION operator eliminates duplicate rows. There is a UNION ALL operator that does not elimiate duplicates, but it is not appropriate here.

Rewriting your query without the DISTINCT clause is a fine solution to this problem:

SELECT code_1
FROM tbl_data
WHERE code_1 IS NOT NULL
UNION
SELECT code_2
FROM tbl_data
WHERE code_2 IS NOT NULL;

It doesn't matter that the two columns are in the same table. The solution would be the same even if the columns were in different tables.

If you don't like the redundancy of specifying the same filter clause twice, you can encapsulate the union query in a virtual table before filtering that:

SELECT code
FROM (
  SELECT code_1
  FROM tbl_data
  UNION
  SELECT code_2
  FROM tbl_data
) AS DistinctCodes (code)
WHERE code IS NOT NULL;

I find the syntax of the second more ugly, but it is logically neater. But which one performs better?

I created a sqlfiddle that demonstrates that the query optimizer of SQL Server 2005 produces the same execution plan for the two different queries:

If SQL Server generates the same execution plan for two queries, then they are practically as well as logically equivalent.

Compare the above to the execution plan for the query in your question:

The DISTINCT clause makes SQL Server 2005 perform a redundant sort operation, because the query optimizer does not know that any duplicates filtered out by the DISTINCT in the first query would be filtered out by the UNION later anyway.

This query is logically equivalent to the other two, but the redundant operation makes it less efficient. On a large data set, I would expect your query to take longer to return a result set than the two here. Don't take my word for it; experiment in your own environment to be sure!

Select distinct of multiple columns from prestodb

You can use group by on all required columns:

SELECT tid, open_dt 
FROM table_name
GROUP BY tid, open_dt 

Counting DISTINCT over multiple columns

If you are trying to improve performance, you could try creating a persisted computed column on either a hash or concatenated value of the two columns.

Once it is persisted, provided the column is deterministic and you are using "sane" database settings, it can be indexed and / or statistics can be created on it.

I believe a distinct count of the computed column would be equivalent to your query.

Selecting distinct values for multiple columns

Seems like you are simply trying to have all the distinct values at hand. Why? For displaying purposes? It's the application's job, not the server's. You could simply have three queries like this:

SELECT DISTINCT [Food Group] FROM atable;

SELECT DISTINCT Name FROM atable;

SELECT DISTINCT [Caloric Value] FROM atable;

and display their results accordingly.

But if you insist on having them all in one table, you might try this:

WITH atable ([Food Group], Name, [Caloric Value]) AS (
  SELECT 'Vegetables', 'Broccoli', 100  UNION ALL
  SELECT 'Vegetables', 'Carrots',  80   UNION ALL
  SELECT 'Fruits',     'Apples',   120  UNION ALL
  SELECT 'Fruits',     'Bananas',  120  UNION ALL
  SELECT 'Fruits',     'Oranges',  90   
),
atable_numbered AS (
  SELECT
    [Food Group], Name, [Caloric Value],
    fg_rank = DENSE_RANK() OVER (ORDER BY [Food Group]),
    n_rank  = DENSE_RANK() OVER (ORDER BY Name),
    cv_rank = DENSE_RANK() OVER (ORDER BY [Caloric Value])
  FROM atable
)
SELECT
  fg.[Food Group],
  n.Name,
  cv.[Caloric Value]
FROM (
  SELECT fg_rank FROM atable_numbered  UNION
  SELECT n_rank  FROM atable_numbered  UNION
  SELECT cv_rank FROM atable_numbered
) r (rank)
  LEFT JOIN (
    SELECT DISTINCT [Food Group], fg_rank
    FROM atable_numbered) fg ON r.rank = fg.fg_rank
  LEFT JOIN (
    SELECT DISTINCT Name, n_rank
    FROM atable_numbered) n  ON r.rank = n.n_rank
  LEFT JOIN (
    SELECT DISTINCT [Caloric Value], cv_rank
    FROM atable_numbered) cv ON r.rank = cv.cv_rank
ORDER BY r.rank

How do I select unique values from multiple columns (each value being unique not the total row)

I would do a union of queries to get all distinct telephones by column then do a query on this union of telephones to get them only once:

SELECT DISTINCT `Tel` FROM
( 
  SELECT DISTINCT `Tel1` AS `Tel` FROM products
  UNION
  SELECT DISTINCT `Tel2` AS `Tel` FROM products
  UNION
  SELECT DISTINCT `Tel3` AS `Tel` FROM products
  UNION
  SELECT DISTINCT `Tel4` AS `Tel` FROM products
) all_telephones

How to do distinct on multiple columns after join and then sort and select latest for each group?

Use DISTINCT ON:

SELECT DISTINCT ON (pg.id, p.prod_id)
    pg.group_name, p.name AS prod_name, v.version
FROM product_group pg
LEFT JOIN product p ON pg.id = p.group_id
LEFT JOIN version v ON v.prod_id = p.prod_id
ORDER BY pg.id, p.prod_id, v.version DESC;

Demo

Get amount of distinct values across multiple columns

Consider below approach

create temp function values(input string) returns array<string> language js as """
  return Object.values(JSON.parse(input));
""";
select *, (
    select count(distinct category) - 1
    from unnest(values(replace(to_json_string(t), 'null', '"null"'))) category
    where category != 'null'
  ) as category_count
from your_table t            

if applied to sample data in y our question - output is

---

Related Topics