Link to home
Start Free TrialLog in
Avatar of sasidhar1229
sasidhar1229Flag for India

asked on

Fetching random row from each group in oracle

I have a table which has row, title, code and store as columns.

I have to group by code and store.

Assume table contains these rows.

Row        Title       Code        Store
      
1       title1      abcd        store1
2       title2      abcd        store1
3       title3      abcd        store1
4       title1      efgh        store2
5       titlex      efgh        store2

select code,store,count(*) from table
group by code,store order by count(*) desc;

It will give :

abcd        store1        3
efgh        store2    2

Now how can we get one random row from each group. For example it should give

Row       Title       Code        Store

2       title2       abcd            store1
5       titlex       efgh            store2

any one of the record from each group.

Regards,
Sasidhar.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

Note, the random_value_agg method might be slightly more intuitive,
but it's not as efficient as the method in the first post.