use dynamic query to get list of columns and concatenate select distinct keyword with it and run that dynamic query

sajhamitra · Mar 19, 2016 1:32 PM

Thanks sajhamitra. But how to concatenate : i) select column_name from all_tab_columns where upper(table_name)='EMP'; with select distinct * from EMP. I mean ,how to concatenate select distinct keyword with the dynamic query to list columns?

Tyo Din · Mar 19, 2016 2:34 PM

SELECT DISTINCTcolumn_name,column_name FROM table_name; SELECT * From table_name; Please understand (*) will return everything, hence DISTINCT *(everything) is a wrong sql statement. DISTINCT specifically looks for unique result set,,The DISTINCT keyword can be used to return only distinct (different) values

WakkaDikka22 · Mar 19, 2016 5:16 PM

WakkaDikka Mitra, I do understand your explanation but i want the unique records in a table having more than 100 columns. I can't list all the column names after distinct keyword.

Tyo Din · Mar 19, 2016 6:14 PM

If your table has a PK all rows should be distinct by definition. If you are trying to just select DISTINCT field but somehow return all other columns what should happen for those columns that have more than one value for a particular field value? so You would need to use GROUP BY and some sort of aggregation on the other columns. select * from table group by column name.

niss · Mar 19, 2016 10:06 PM

try this select distinct a.* from my_table a;

kunthaun · Mar 20, 2016 12:04 AM

Thanks Kunthaun; it worked.

Tyo Din · Mar 20, 2016 6:06 PM