Home » SQL & PL/SQL » SQL & PL/SQL » Random Selections (Oracle 9i, Toad, Windows XP Build 2600.sp2)
Random Selections [message #402109] Thu, 07 May 2009 11:40 Go to next message
david.redden1973
Messages: 1
Registered: May 2009
Location: TEMECULA
Junior Member
I am looking to return results from a SQL query that have the following (somewhat random but not all the way)

Results typically would return about 1500 records
in these records are UserIDs. I want to return 2 records for each User ID (those records need to be what are random)
So if UserID1 has a total of 20 records within the 1500 records, I want to return only 2 random records, and so on down the list of USERIDs
There's not that many user IDs - approximately 20

I started using dbms_random.value, but am having writer's block at this point.
Can easily just randomly bring in 2 random records for any one of the 20 users. But I need to make sure I get 2 random records for every user.

Is this possible, if so, how...PLEASE HELP...

Thanks
Re: Random Selections [message #402113 is a reply to message #402109] Thu, 07 May 2009 11:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
First thought that comes to mind:
Analytical:

Rank() over (partition by userid order by dbms_random) as rn

And then pick the rows with rn < 3

[Not tested of course Smile]

[Updated on: Thu, 07 May 2009 11:49]

Report message to a moderator

Re: Random Selections [message #402114 is a reply to message #402109] Thu, 07 May 2009 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use row_number function and partition by id order by random and get only the first 2 ones.

Regards
Michel
Re: Random Selections [message #402115 is a reply to message #402109] Thu, 07 May 2009 11:51 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Results typically would return about 1500 records
>There's not that many user IDs - approximately 20
>I want to return 2 records for each User ID
2*20 is no where close to 1500.

What would happen if the 2 returned records were "not random" & how would the software realize they were "not random"?


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Take a look at SAMPLE qualifier on SELECT statement.
Re: Random Selections [message #402117 is a reply to message #402115] Thu, 07 May 2009 11:53 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What would happen if the 2 returned records were "not random" & how would the software realize they were "not random"?

That's a very good question!
Correction: 2 very good questions.

Regards
Michel

[Updated on: Thu, 07 May 2009 11:55]

Report message to a moderator

Previous Topic: [Help] Partially overlapped time calculation
Next Topic: Insert with Select Inner Join
Goto Forum:
  


Current Time: Sat Dec 10 10:51:34 CST 2016

Total time taken to generate the page: 0.04532 seconds