Home » SQL & PL/SQL » SQL & PL/SQL » To get some 10 out of n for each attribute in the same table.
To get some 10 out of n for each attribute in the same table. [message #192415] Tue, 12 September 2006 04:24 Go to next message
anandy59
Messages: 2
Registered: September 2006
Junior Member
I am having a table,...with fields account and client_id

a client have so many account's ,,like that so many clients will be there in that table.

now i want...for each client just 10 account's are to be retrieved.

For each client, its 10 out of n accounts are to be retrieved..dont bother abvout order..just randomly 10 accounts..

its urgent...plzz
Re: To get some 10 out of n for each attribute in the same table. [message #192429 is a reply to message #192415] Tue, 12 September 2006 06:22 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
SELECT x.client_id
      ,x.account
FROM   (SELECT client_id
              ,account
              ,rank() over(PARTITION BY client_id ORDER BY client_id) rank_acc) x
WHERE  x.rank_acc <= 10
Re: To get some 10 out of n for each attribute in the same table. [message #192462 is a reply to message #192415] Tue, 12 September 2006 08:44 Go to previous messageGo to next message
jaydeep mitra
Messages: 20
Registered: August 2006
Location: India
Junior Member
hi,

Select client_id,acc_id from table where client_id=1001 and rownum<11
union
Select client_id,acc_id from table where client_id=1001 and rownum<11
Hope this solves your problem

Regards
Jaydeep
Re: To get some 10 out of n for each attribute in the same table. [message #192464 is a reply to message #192462] Tue, 12 September 2006 08:51 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
jaydeep mitra wrote on Tue, 12 September 2006 09:44


Select client_id,acc_id from table where client_id=1001 and rownum<11
union
Select client_id,acc_id from table where client_id=1001 and rownum<11



How is this an answer to the OP question? First of all, the select after the union offers no additional rows to the output, unless my eyes are deceiving me or my brain is just not processing what my eyes see.

[Updated on: Tue, 12 September 2006 08:53]

Report message to a moderator

Re: To get some 10 out of n for each attribute in the same table. [message #192626 is a reply to message #192429] Wed, 13 September 2006 00:52 Go to previous message
anandy59
Messages: 2
Registered: September 2006
Junior Member


Thanks for the solution...i got the answer
Previous Topic: ORA-ORA-00604 and ORA-01400 problem while executing stored procedure
Next Topic: last value in cursor active set
Goto Forum:
  


Current Time: Tue Dec 06 12:03:04 CST 2016

Total time taken to generate the page: 0.19246 seconds