Home » SQL & PL/SQL » SQL & PL/SQL » selecting random recordset (oracle 9.2.0.3)
selecting random recordset [message #404267] Thu, 21 May 2009 02:48 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
create table  order_dtls( cutomer_id number(10), order_id(7));

insert into order_dtls values(1234567,1); 
insert into order_dtls values(1234567,2); 
insert into order_dtls values(1234567,3); 
insert into order_dtls values(1234567,4); 
insert into order_dtls values(1234567,5); 
insert into order_dtls values(1234461,1); 
insert into order_dtls values(1234461,2); 
insert into order_dtls values(1234461,3); 
insert into order_dtls values(1234761 ,1); 
insert into order_dtls values(1234761 ,2); 
insert into order_dtls values(1234761 ,3); 
insert into order_dtls values(1234761 ,4); 
insert into order_dtls values(1234761 ,5); 
insert into order_dtls values(1234761 ,6); 
insert into order_dtls values(1234761 ,7); 
insert into order_dtls values(1234467 ,1); 
insert into order_dtls values(1234467,2); 
insert into order_dtls values(1234467 ,3); 




I am getting a value for order_id from some function and now need to get the random customer_id with all order_id's
which have order_id value 3 but not those which have order id values more than 3
Re: selecting random recordset [message #404271 is a reply to message #404267] Thu, 21 May 2009 02:54 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Good stuff! Have you made any attempts yet or are you just keeping us abreast of your current status?
Re: selecting random recordset [message #404278 is a reply to message #404271] Thu, 21 May 2009 03:08 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
SELECT CUSTOMER_ID
FROM (  SELECT CUSTOMER_ID, MAX(ORDER_ID) FROM ORDER_DTLS 
        HAVING MAX(ORDER_ID)=3
        GROUP BY CUSTOMER_ID
        ORDER BY DBMS_RANDOM.value) 
WHERE ROWNUM = 1);
							

Is there any better way to do?

[Updated on: Thu, 21 May 2009 03:10]

Report message to a moderator

Re: selecting random recordset [message #404279 is a reply to message #404278] Thu, 21 May 2009 03:17 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Seems fine to me other than the syntax errors which would prevent the code as you have posted it from running.
Re: selecting random recordset [message #404280 is a reply to message #404278] Thu, 21 May 2009 03:40 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need to select max value in the inner query as you don't use it in the outer one.

Regards
Michel
Previous Topic: sql select query
Next Topic: How to find Name of the private procedures/functions inside the package?
Goto Forum:
  


Current Time: Sat Dec 10 18:23:44 CST 2016

Total time taken to generate the page: 0.08341 seconds