Home » SQL & PL/SQL » SQL & PL/SQL » last 2 (oracle9i)
last 2 [message #432503] Tue, 24 November 2009 10:00 Go to next message
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
Member
Hi experts
i have the following data
SNO SNAME FEE
A1 rajesh 1500
A1 ravi 1505
A1 ramana 1502
A1 suresh 1508
A2 manju 1680
A2 prakash 1686
A2 mohan 1680
A2 swapna 1683

I want to retrieve last two roes for each group i.e
the output like this

SNO SNAME FEE
A1 ramana 1502
A1 suresh 1508
A2 mohan 1680
A2 swapna 1683

Please help me thanks in advance.
Re: last 2 [message #432510 is a reply to message #432503] Tue, 24 November 2009 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Top N query is FAQ, please search BEFORE posting.

Also read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Use SQL*Plus and copy and paste what you already tried.

Regards
Michel
Re: last 2 [message #432518 is a reply to message #432503] Tue, 24 November 2009 12:29 Go to previous messageGo to next message
joy_division
Messages: 4617
Registered: February 2005
Location: East Coast USA
Senior Member
There is no such thing as "last" in a relational database. You have to define the order. without something like a sequence or date, your results would be random, as in your case (based on the data I see).
Re: last 2 [message #432525 is a reply to message #432503] Tue, 24 November 2009 16:28 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Let me add to Joy's response.

Here is your data:
SNO SNAME FEE
A2 swapna 1683
A2 mohan 1680
A2 prakash 1686
A2 manju 1680
A1 suresh 1508
A1 ramana 1502
A1 ravi 1505
A1 rajesh 1500
Why did you say there were the last two rows in group A1? Same question for group A2.
SNO SNAME FEE
A1 ramana 1502
A1 suresh 1508


Kevin
Re: last 2 [message #432559 is a reply to message #432525] Tue, 24 November 2009 23:05 Go to previous messageGo to next message
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
Member
I have partition the table according to the sno
i have done this one

select sno,sname,fee,
row_number()over(partition by sno order by sno)as rn
from students;

for this i got four records for each SNO,

in that i want to retrieve only last two values for each SNO
Re: last 2 [message #432562 is a reply to message #432559] Tue, 24 November 2009 23:12 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
read previous responses
Your question has been answered
Re: last 2 [message #432604 is a reply to message #432559] Wed, 25 November 2009 03:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
YOur query should be
select sno,sname,fee,
row_number()over(partition by sno order by fee desc)as rn
from students


Now, look at the results from that and see if you can work out a way to get the rows you want.

Hint - use your query as an inline view.
Re: last 2 [message #432656 is a reply to message #432559] Wed, 25 November 2009 09:27 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
prakashaa

OK except that your query is insufficient to correctly give the "last" records.

select sno,sname,fee,
row_number()over(partition by sno order by sno)as rn
from students;

Consider this for partition A1:

SNO SNAME FEE
A1 suresh 1508
A1 ramana 1502
A1 ravi 1505
A1 rajesh 1500

Last two records are ravi/rajesh.

Now consider this:

SNO SNAME FEE
A1 ravi 1505
A1 rajesh 1500
A1 suresh 1508
A1 ramana 1502

Last two records are suresh,ramana.

Now consider this:

SNO SNAME FEE
A1 ravi 1505
A1 suresh 1508
A1 ramana 1502
A1 rajesh 1500

Last tworecords are ramana,rajesh.

And so it goes. All three of theses datasets are the same, they have the same data, and they all satisfy your partition/order by on SNO. Yet each set of rows gives a different answer for "what are the last two rows". Why?

Your query will return different results depending upon the query plan oracle uses to get the data which can depend upon indexes and physical ordering of rows. Your rownum expression needs to provide additional sorting and that sorting needs to be on a set of attributes that provide uniqueness, in order to guarantee that the same two rows will always be identified as the last two rows in each partition. To get consistent answers using analytics, your sorting must be correct. This is what I was trying to help you understand with my prior post.

So what must you do to your rownum() expression in order to fix your query so that the last two rows in each group will always be the same?

Kevin

[Updated on: Wed, 25 November 2009 09:31]

Report message to a moderator

Previous Topic: Order by in a cursor
Next Topic: inserting more than one record in a single transaction
Goto Forum:
  


Current Time: Wed Sep 28 15:57:30 CDT 2016

Total time taken to generate the page: 0.06305 seconds