## How to get nth highest salary using SQL/400 query

2465 pts.
Tags:
AS/400
SQL
SQL/400
Hi,
How can I get nth highest salary from a PF named EMP having two columns EMPID and ESAL need to retrieve nth highest salary using SQL/400 query. Here N may be first, second,third any no.
Thanks!

Software/Hardware used:
AS400

Thanks. We'll let you know when a new response is added.

## Discuss This Question: 10 Replies

Thanks. We'll let you know when a new response is added.
• Sounds like homework. Post your code and we will take a look.
report
• Good homework assignment but you won't learn unless you solve it yourself.

report
• Do you have to use SQL/400 query?
report
• The professors really need to update course material. This question was asked and answered in this forum back in 2006. We don't mind helping with problems as long as you tried and did some research on your own. It took me all of about 15 seconds to find the post from 2006.
report
• Below SQL query provides second highest salary but still struggling to find nth or generic way to get nth highest salary:-

SQL query to get second highest salary:-

SELECT * FROM JKJK2/EMP
WHERE ESAL    =
(SELECT MAX(ESAL) FROM EMP
WHERE ESAL <> (SELECT MAX(ESAL) FROM EMP) )

select * from jkjk2/emp

Position to line  . . . . .
....+....1....+....2....+....3....+....4....
EMPID   EMPNAME                      ESAL
1   David                      25,000
2   john                       30,000
3   nikhil                     45,000
4   amit                       32,000
5   donald                     60,000
********  End of data  ********
Output--> 3 nikhil 45000
report
• Out put was the result of above posted SQL query which correctly gives second highest salary from a physical file EMP but how to generalize it to obtain nth( n  can be 1st,second ,third...any number) highest salary.

Thanks
report
• The way to make it be a random selected number would require the passing of a parameter.. How else is the program to know what ?th number you are looking for ?
report
• Program is not needed here only SQL query is required here which should give nth highest salary from emp physical file or table.

Thanks
report
• Here is a very rough pass at a possible answer

Actually my inner most query would be

Select esal from JKJK2/EMP order by esal fetch first nnn rows only

That would make a list with your desired value as the lowest

Select min(esal) from

(Select esal from JKJK2/EMP order by esal fetch first nnn rows only)

this would get me the nnn th salary

Select * from JKJK2/EMP where esal =

(

Select min(esal) from

(Select esal from JKJK2/EMP order by esal fetch first nnn rows only))

But how to get the variable nnn into a query .. in query manager or in RPG you would populate a variable.

Perhaps an alternative would be to have a  data file NthVal with one value in it .. something like this

Select * from JKJK2/EMP where esal =

(

Select min(esal) from

(Select esal from JKJK2/EMP order by esal fetch first (select nnn from NthVal) rows only))

report
• Simpler solution - two sql's

1.

Create table JKJK2/EMP2 as

(Select * from JKJK2/EMP order by esel)  with data;

2.

Select * from JKJK2/EMP2 x where RRN(x) = nnn

that could be the one value in the second file NthVal

Select * from JKJK2/EMP2 x where RRN(x) = (select nnn from NthVal)