Home » SQL & PL/SQL » SQL & PL/SQL » Rownum issue
Rownum issue [message #220195] Mon, 19 February 2007 07:28 Go to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

SELECT ENAME,SAL FROM (SELECT EMP.*,ROWNUM S FROM EMP ORDER BY SAL DESC) WHERE S = 2

This is retrieving the third max sal and not the second max.....but still the rownum does not work when i put in it in the following way...
SELECT ENAME,SAL FROM (SELECT EMP.*,ROWNUM S FROM EMP ORDER BY SAL DESC) WHERE S > 2

can anybody help me?
Re: Rownum issue [message #220198 is a reply to message #220195] Mon, 19 February 2007 07:40 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
OraFAQ
If you want to change the query, which has been written by you
check this
SELECT ENAME,SAL 
  FROM (SELECT a.*,ROWNUM S 
          FROM (select * from scott.EMP 
                 ORDER BY SAL DESC) a) 
 WHERE S = 2
But this will give only one row with the second highest salary.
By
Vamsi
Re: Rownum issue [message #220199 is a reply to message #220198] Mon, 19 February 2007 07:42 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Thank you.. That worked...
Re: Rownum issue [message #220202 is a reply to message #220199] Mon, 19 February 2007 07:53 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Now i have another issue...

If there is a duplicate,how do i retrieve the 2nd max sal??

Re: Rownum issue [message #220204 is a reply to message #220202] Mon, 19 February 2007 08:01 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
In this case, you should consider DENSE_RANK.
Re: Rownum issue [message #220207 is a reply to message #220202] Mon, 19 February 2007 08:10 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Please check the link, which I have given in my previous post.

By
Vamsi
Re: Rownum issue [message #220208 is a reply to message #220207] Mon, 19 February 2007 08:21 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

YUP, That link was great. Thanks a lot.
Re: Rownum issue [message #220261 is a reply to message #220208] Mon, 19 February 2007 23:43 Go to previous message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
many ways to get second maxium sal
see this

select * from (select a.*, rownum s from (select sal from emp order by sal desc) a ) where
s=3

select max(sal) second from emp where sal!=(select max(sal) from emp)

select distinct(sal) from emp e where 3 = (select distinct(count(*)) from emp where
sal > e.sal)

select sal from (select ename,sal, dense_rank( ) over(order by sal desc) r from emp)
where r=2

thanks
srinivas
Previous Topic: Hi i am a newbie
Next Topic: How to convert numbers to words (In Rupees format)
Goto Forum:
  


Current Time: Sun Dec 04 10:34:16 CST 2016

Total time taken to generate the page: 0.10079 seconds