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.

Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Try this:

select title, code, store from (
select title, code, store, row_number() over(partition by code, store order by dbms_random.value) myrn
from table
where myrn=1
sdstuberConnect With a Mentor Commented:

SELECT id, title, code, store
  FROM (SELECT t.*, random_value_agg(id) OVER (PARTITION BY store) rand_id
          FROM yourtable t)
 WHERE id = rand_id
Note, the random_value_agg method might be slightly more intuitive,
but it's not as efficient as the method in the first post.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.