Second max [message #606404] |
Thu, 23 January 2014 06:49 |
|
Lynn321
Messages: 1 Registered: January 2014
|
Junior Member |
|
|
I have used a query : select sal from emptab where empid in (2,4,7) and rownum = 2 order by sal desc;
The usage rownum doesn't select any rows, row number doesn't work. iam trying to select the second maximum salary from a salary list. Can anyone suggest how to fetch this second max successfully. Note empid stands for employee Id and emptab for employee table and sal for salary.
please help me with this.
Thank you,
Lynn321
|
|
|
|
Re: Second max [message #606407 is a reply to message #606404] |
Thu, 23 January 2014 07:12 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Lynn321 wrote on Thu, 23 January 2014 12:49I have used a query : select sal from emptab where empid in (2,4,7) and rownum = 2 order by sal desc;
Close, but no cigar, Blackswan's description of the scenario should help clear up why. As to getting what you actually want, do a search on TOP-N there are many many examples on how to do this. Can we assume that either the example that you supplied is massively simplified or that this is a homework assignment? If the latter, then there is a good chance that you will be expected to perform the task using one or two of the many techniques available to you, pay attention to the context of the question and the subject/ module in which it is being asked.
Quote:The usage rownum doesn't select any rows, row number doesn't work. More accurately, it doesn't do what you thought it would do, it's just that what you thought it would do isn't what it actually does. Doesn't mean that rownum doesn't work
|
|
|
Re: Second max [message #606408 is a reply to message #606404] |
Thu, 23 January 2014 07:15 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Lynn321 wrote on Thu, 23 January 2014 18:19Can anyone suggest how to fetch this second max successfully.
Simple google search for TOP N-query would return millions of results.
|
|
|
|