sasidhar1229
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
but it's not as efficient as the method in the first post.