Random Data Set - 1 Row Per Person 1

[mwolf00]

Hi,

I am trying to write a query that will give me one random row per user. Let's say the table has a userName column and a requestNumber column. The requestNumber column is unique so that one user may have many requests. Can I write a query that will list each user once with a random request number?

This is as close as I've come...

SELECT userName, max(ranVal)
FROM (Select userName, requestNumber, dbms_random.value ranVal
from myTable)subTable
GROUP BY userName

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[SantaMufasa]

How are the "RequestNumbers" assigned and distributed for each "UserName"?[ul][li]Example 1: For each user, request numbers start with one and increment by 1 until the numbers reach their current maximum for that user.[/li][li]Example 2: Users' RequestNumbers are assigned from a sequence used by all UserNames, so there is no way to predict the starting, next, or current value when looking at the RequestNumbers for a specific user.[/li][/ul]Your response to the above question affects significantly the code to do what you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[mwolf00]

Thank you for the reply. Example #2 is more accurate. The RequestNumber field is actually a 17 digit alpha-numeric field that really isn't sequential at all.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[SantaMufasa]

MWolf,

Since you posted no data for me to use, I was unable to test my code, below, but I hope it works for you:

SELECT userName, x.RequestNumber
  from (select UserName
              ,RequestNumber
              ,row_number() over (partition by UserName order by RequestNumber) Req_Sequence
          from myTable) x
      ,(select UserName, trunc(dbms_random.value(1,cnt)) rnd
          from (select UserName, count(*) cnt from myTable group by UserName)) y
 where x.UserName = y.UserName
   and req_seq = rnd
 order by UserName
/

Let me know if it does what you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[mwolf00]

Wow, I have no idea how this works but it works great. Thank you very much. I'm sorry for the lack of sample data but I was already simplifying this a whole lot. Here is my final solution

 SELECT x.txtAssignedAnalyst, f.efoldername, f.estagename
 FROM (
 	  SELECT txtAssignedAnalyst, efolderid, row_number() over (PARTITION BY txtAssignedAnalyst ORDER BY efolderid) req_sequence
	  FROM rbm_management WHERE TRUNC(dtActualDay2Complete) = TO_DATE('2/14/2008','mm/dd/yyyy') 
	  	   OR (TRUNC(dtWithdrawn) = TO_DATE('2/14/2008','mm/dd/yyyy') AND dtdeadlinestart > TO_DATE('1900','yyyy'))
 )x
 , (
   	  SELECT txtAssignedAnalyst, TRUNC(dbms_random.value(1,cnt)) rnd
	  FROM (SELECT txtAssignedAnalyst, COUNT(*) cnt FROM rbm_management 
	  WHERE TRUNC(dtActualDay2Complete) = TO_DATE('2/14/2008','mm/dd/yyyy') 
	  	   OR (TRUNC(dtWithdrawn) = TO_DATE('2/14/2008','mm/dd/yyyy') AND dtdeadlinestart > TO_DATE('1900','yyyy'))
	  GROUP BY txtAssignedAnalyst)
 ) y, eFolder f
 WHERE x.txtAssignedAnalyst = y.txtAssignedAnalyst
 AND x.efolderid = f.efolderid
 AND req_sequence = rnd
 ORDER BY x.txtAssignedAnalyst

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[SantaMufasa]

Wow, I have no idea how this works but it works great.

Yes, Oracle Analytics are a "Great Leap Forward". You can read and learn more about Oracle Analytics, and see excellent coding examples, at this link: [link]http://www.orafaq.com/node/55 Analytic Functions by Example[/url], by Shouvik Basu.


Let us know your evaluation of Shouvik's page.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[mwolf00]

Let us know your evaluation of Shouvik's page.

I think it will take me a while to absorb the whole page, but I have been through the first few examples and I have found them well written and extremely helpful. I will have to try to work them into my queries and try to get used to using them.

Thank you again.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook