|
View previous topic :: View next topic
|
| Author |
Message |
king.hyd
New User
Joined: 28 May 2007
Posts: 6
Location: Hyderabad
|
|
|
|
Let's say we have a table given below with emp name and emp salary.
How to get 2nd highest salary from the below table. Can someone provide the query?
A | 3000
B | 1500
C | 4000
D | 7000
E 2000 |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Maybe you should try to search the db2 forum for second max ? |
|
| Back to top |
|
|
kumar32149
New User
Joined: 22 Sep 2010
Posts: 9
Location: India
|
|
|
|
| select max(sal) from emp where sal<(select max(sal) from emp); |
|
| Back to top |
|
|
manikawnth
New User
Joined: 07 Feb 2007
Posts: 61
Location: Mumbai
|
|
|
|
Its good to use the above suggestion if u r doing in some QMF to just verify.
When you do it embedded, always use a cursor who knows u may get more than one second best salaries. |
|
| Back to top |
|
|
Ronald Burr
Active User
Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.
|
|
|
|
| manikawnth wrote: |
| When you do it embedded, always use a cursor who knows u may get more than one second best salaries. |
If you are only retrieving column data that is qualified by MIN/MAX or other aggregate functions, there is no need to use a cursor.
There can logically be no instance where there could be more than one second highest salary. Even if there were a thousand qualifying rows that contain the same exact second highest salary, the MAX aggregate will result in only one returned value. |
|
| Back to top |
|
|
manikawnth
New User
Joined: 07 Feb 2007
Posts: 61
Location: Mumbai
|
|
|
|
| Quote: |
| Even if there were a thousand qualifying rows that contain the same exact second highest salary, the MAX aggregate will result in only one returned value. |
My intention was to say him, if he needs the employee details (like name) who get the 2nd highest salary, he needs to fetch it.
If he just needs the figure it is not necessary. |
|
| Back to top |
|
|
|
|
