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
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    Sounds like homework. Post your code and we will take a look.
    132,650 pointsBadges:
    report
  • philpl1jb

    Good homework assignment but you won't learn unless you solve it yourself.

    54,090 pointsBadges:
    report
  • CharlieBrowne
    Do you have to use SQL/400 query?
    62,385 pointsBadges:
    report
  • ToddN2000
    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.
    132,650 pointsBadges:
    report
  • 6r
    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
    2,465 pointsBadges:
    report
  • 6r
    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
    2,465 pointsBadges:
    report
  • ToddN2000
    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 ?
    132,650 pointsBadges:
    report
  • 6r
    Program is not needed here only SQL query is required here which should give nth highest salary from emp physical file or table.

    Thanks
    2,465 pointsBadges:
    report
  • philpl1jb

    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))

    54,090 pointsBadges:
    report
  • philpl1jb

    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)

    54,090 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: