Solved

# Fetching random row from each group in oracle

Posted on 2013-06-06
1,271 Views
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.
0
Question by:sasidhar1229
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 450 total points
ID: 39226080
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
/
0

LVL 74

Assisted Solution

sdstuber earned 50 total points
ID: 39226137
http://www.experts-exchange.com/Database/Oracle/A_9391-How-to-Create-User-Defined-Aggregates-in-Oracle.html

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
0

LVL 74

Expert Comment

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…