|
View previous topic :: View next topic
|
| Author |
Message |
kannag
New User
Joined: 02 Aug 2006
Posts: 23
Location: chennai
|
|
|
|
hi all,
how can i retrieve maximum salary from the table and second highest saalry from the table too. |
|
| Back to top |
|
 |
khamarutheen
Active Member
Joined: 23 Aug 2005
Posts: 677
Location: NJ
|
|
|
|
kannag,
Hope the below query will help 
| Quote: |
| SELECT MAX(SAL) FROM EMP WHERE < (SELECT MAX(SAL) FROM EMP ORDER BY DESC) |
|
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
Hi kannag,
Here are two SELECTs that will work for you.
The first will return one row with two columns, Highest and Second_highest salary, and the second will return two rows with one column, First row highest, second second_highest salary.
| Code: |
SELECT S2.SALARY AS HIGHEST,
MAX(S1.SALARY) AS SECOND_HIGHEST
FROM DB2.EMP S1,
(SELECT MAX(SALARY) AS SALARY
FROM DB2.EMP
) S2
WHERE S1.SALARY < S2.SALARY
GROUP BY S2.SALARY
;
SELECT DISTINCT SALARY
FROM DB2.EMP
ORDER BY SALARY DESC
FETCH FIRST 2 ROWS ONLY
;
|
Dave |
|
| Back to top |
|
|
umed
New User
Joined: 13 May 2005
Posts: 38
|
|
|
|
Try following query
Select sal from ( select sal from EMP order by sal Desc) fetch first 2 rows only
Please correct if I am wrong
Thanks,
Umed |
|
| Back to top |
|
|
|
|
