This isn't a SqlExplorer question, it's a general SQL question that is likely to be better answered in support forums for your paricular database server vendor.
I'll try and answer your question though; my database is Oracle so if your's isn't you may have problems getting this to work but the principal will be the same:
select v.*, dbms_random.random as my_random from visits v order by my_random
would return the visits table with an extra column called my_random. If you only want the first 10 rows, you have to do this:
select * from (select v.*, dbms_random.random as my_random from visits v order by my_random) where rownum < 11
The original query is now a subquery and the "rownum" restricts it to the first 10 rows. If you want to join this to your members table, you'll have to make another subquery:
select * from members m, (select * from (select v.*, dbms_random.random as my_random from visits v order by my_random) where rownum < 11) temp
where m.member_id = temp.member_id
If you can't do subqueries in your database, you'll have to populate a temporary table and then join against that table. EG:
John
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I work in a health care and have a project that needs to pull the following information:
I need to pull a list of 10 members seen in the past year for a provider with 50 or more members in their panel.
I can pull the providers of 50 members, I can pull the visits by their members.
My question is how can I pull a random number of 10 visits for each of the providers. I can have over 200 providers with panels greater than 50.
Any help?
This isn't a SqlExplorer question, it's a general SQL question that is likely to be better answered in support forums for your paricular database server vendor.
I'll try and answer your question though; my database is Oracle so if your's isn't you may have problems getting this to work but the principal will be the same:
select v.*, dbms_random.random as my_random from visits v order by my_random
would return the visits table with an extra column called my_random. If you only want the first 10 rows, you have to do this:
select * from (select v.*, dbms_random.random as my_random from visits v order by my_random) where rownum < 11
The original query is now a subquery and the "rownum" restricts it to the first 10 rows. If you want to join this to your members table, you'll have to make another subquery:
select * from members m, (select * from (select v.*, dbms_random.random as my_random from visits v order by my_random) where rownum < 11) temp
where m.member_id = temp.member_id
If you can't do subqueries in your database, you'll have to populate a temporary table and then join against that table. EG:
John