Home » SQL & PL/SQL » SQL & PL/SQL » need help in PLSQL
need help in PLSQL [message #265052] Wed, 05 September 2007 05:54 Go to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
i have several recods for a client who trades but i wants to retrive only frist two records of the trades comiited by a particular client & the total clients are 25600

this is the basic qurey

select distinct cnm.cnm_ent_id, cnm_date,
cnm.cnm_brokerage_amt Brokerage,
sum(cd.cnd_qty * cd.cnd_price) Turnover
from contract_note_master cnm,contract_note_details cd
where cnm.cnm_no = cd.cnd_cnm_no
and cnm.cnm_sd_stage <> 'C'
and cd.cnd_stage <> 'C'
and cnm.cnm_ent_id in ('30000' , '270576', '307246')
group by cnm_ent_id,cnm_date,cnm_brokerage_amt
order by cnm_ent_id,cnm_date


plz help me
Re: need help in PLSQL [message #265056 is a reply to message #265052] Wed, 05 September 2007 06:00 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Two retrieve arbitrary two records use rownum<3

Use order by as necessary.
Re: need help in PLSQL [message #265057 is a reply to message #265052] Wed, 05 September 2007 06:00 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

information given by you is insufficient to understand your requirement.ananlytical functions might of some help to you.




regards,
Re: need help in PLSQL [message #265058 is a reply to message #265052] Wed, 05 September 2007 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Then, you can
select * from (<your query>) where rownum <= 2

Regards
Michel
Re: need help in PLSQL [message #265064 is a reply to message #265058] Wed, 05 September 2007 06:06 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
thanks for your advice

& i have tried it before but rownum gives me only first two rows of first client rest of the client are not diasplyed in result set

plz advice some other way


Re: need help in PLSQL [message #265070 is a reply to message #265064] Wed, 05 September 2007 06:15 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

you can try something like this :

select deptno,ename,sal from(
select deptno,ename,sal,row_number()over(partition by deptno order by sal desc) sal_rnk
from emp)
where sal_rnk <3 



regards,

[Updated on: Wed, 05 September 2007 06:16]

Report message to a moderator

Re: need help in PLSQL [message #265073 is a reply to message #265070] Wed, 05 September 2007 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't do that.
To get all row_number Oracle has to keep in memory the whole result set (possibly swapping it in temporary segment).
With rownum, Oracle only keep the 2 first rows it needs.

Regards
Michel
Re: need help in PLSQL [message #265074 is a reply to message #265064] Wed, 05 September 2007 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i have tried it before but rownum gives me only first two rows of first client rest of the client are not diasplyed in result set

But the question was:
Quote:
i wants to retrive only frist two records of the trades comiited by a particular client

So what is the rest of clients? You only want ONE client.

Post a precise question if you want accurate answer.

Regards
Michel
Re: need help in PLSQL [message #265079 is a reply to message #265057] Wed, 05 September 2007 06:26 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
i have 1 client performing many trades but i just want 1st 2 trade of that client

there are 25600 client
& i have tried rownum function also it give only 1 client data at a time

i hope it's ok now
Re: need help in PLSQL [message #265082 is a reply to message #265079] Wed, 05 September 2007 06:36 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I would probably use an analytic function for that. And here's a clue how to start.

MHE
Re: need help in PLSQL [message #265097 is a reply to message #265074] Wed, 05 September 2007 07:24 Go to previous message
sonalshastry
Messages: 52
Registered: September 2007
Member
i am sorry

and plz let me put my point once again

awaiting for your reply
Thanking you
Previous Topic: Exchange partition
Next Topic: Oracle Objects
Goto Forum:
  


Current Time: Sat Dec 03 08:23:00 CST 2016

Total time taken to generate the page: 0.10961 seconds