Distinct values in multiple columns

sql distinct sap-ase
oortcloud_domicile picture oortcloud_domicile · Aug 13, 2012 · Viewed 13.8k times · Source

I am trying to get distinct values for multiple columns in table.

for example if my table has COL1,COL2,COL3 as columns and I am running a query like

SELECT DISTINCT COL1, COL2, COL3 FROM TABLEx

but what happens is depending on the data in one of the columns I am getting duplicate values in two other columns. If COL1 has 40 unique values and COL2 has only 10 unique values and COL3 has 5 unique values. I am getting 40 rows of data where COL1 is unique and COL2/COL3 has duplicate values. How do i get DISTINCT values for each column and replace the duplicate values by null values. I am doing this only to get distinct values for my drop down lists in a web page. I can always perform seperate query for each field but I think it is not very effective. Any help as I how I approach this?

Answer

Mark Byers picture Mark Byers · Aug 13, 2012

I can always perform seperate query for each field

Yes! Do this!

but i think it is not very effective.

It's probably fine. You can improve performance by adding a separate index to each column. If you are still worried that it will be slow, measure it to be sure. If it turns out that it really is a problem then you may want to consider using caching.

Related questions

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

My table is: id home datetime player resource ---|-----|------------|--------|--------- 1 | 10 | 04/03/2009 | john | 399 2 | 11 | 04/03/2009 | juliet | 244 5 | 12 | 04/03/2009 | borat | 555 3 | 10 | 03/03/2009 | john | 300 4 | 11 | 03/03/2009 | juliet | 200 6 | 12 | 03/03/2009 | borat | 500 7 | 13 | 24/12/2008 | borat | 600 8 | 13 | 01/01/2009 | borat | 700 I need to select each distinct home holding the maximum value of datetime. Result would be: id home datetime …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
SQL/mysql - Select distinct/UNIQUE but return all columns?

SELECT DISTINCT field1, field2, field3, ...... FROM table I am trying to accomplish the following sql statement but I want it to return all columns is this possible? Something like: SELECT DISTINCT field1, * from table

Read More