SAJHA MOBILE
Oracle simple help
Posts 8 · Viewed 3689 · Likes 1 · Go to Last Post
Tyo Din
· Snapshot 0
Like · Likedby · 0
I need to : select distinct * from my_table; it's not working.

What is alternative to this query? I can't use column_names in place of * because there are hundreds of columns in my table.

Thanks IT sajha Experts for ur answer.
sajhamitra
· Snapshot 37
Like · Liked by · 0
use dynamic query to get list of columns and concatenate select distinct keyword with it and run that dynamic query
Tyo Din
· Snapshot 42
Like · Liked by · 0
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?
WakkaDikka22
· Snapshot 124
Like · Liked by · 0
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
Tyo Din
· Snapshot 155
Like · Liked by · 0
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.
niss
· Snapshot 224
Like · Liked by · 1
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.
kunthaun
· Snapshot 257
Like · Liked by · 0
try this

select distinct a.* from my_table a;
Tyo Din
· Snapshot 366
Like · Liked by · 0
Thanks Kunthaun; it worked.
Please log in to reply to this post

You can also log in using your Facebook
View in Desktop
What people are reading
You might like these other discussions...
· Posts 90 · Viewed 98735 · Likes 18
· Posts 13 · Viewed 5286 · Likes 1
· Posts 1258 · Viewed 146513 · Likes 492
· Posts 202 · Viewed 25907 · Likes 81
· Posts 6 · Viewed 968
· Posts 33 · Viewed 2167 · Likes 15
· Posts 8 · Viewed 2376
· Posts 18 · Viewed 3345 · Likes 9
· Posts 7 · Viewed 871
· Posts 11 · Viewed 1263



Travel Partners
CheapAirFaresToNepal
Nepas Travel Tours
Himalayan Treasures & Travels
Aero Nepal
Air Zone
Travel House Nepal
Zen Travels
Travel Solution USA