SAJHA MOBILE
Oracle simple help
Posts 8 · Viewed 4086 · 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 593 · Viewed 28774 · Likes 843
· Posts 7 · Viewed 412 · Likes 6
· Posts 8 · Viewed 567 · Likes 8
· Posts 4 · Viewed 240 · Likes 3
· Posts 389 · Viewed 76339 · Likes 137
· Posts 59 · Viewed 2987 · Likes 33
· Posts 6 · Viewed 423 · Likes 4
· Posts 5 · Viewed 198 · Likes 4
· Posts 10 · Viewed 404 · Likes 3
· Posts 41 · Viewed 14124 · Likes 19



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