AS/400-SQL/400 query

620 pts.
Tags:
AS/400
SQL
Write an SQL query to the 6th highest salary of employee? I know how to write query for 6th highest salary of employee but it's too lengthy. Can any one simplify this query please.
2nd highest salary: select max(esal) from emp where esal <(select max(esal) from emp)
3rd highest salary: select max(esal) from emp where esal <(select max(esal) from emp where esal <(select max(esal) from emp))
.
.
6th highest salary:

select max(esal) from emp where esal <(select max(esal) from emp where esal <(select max(esal) from emp  where esal <(select max(esal) from emp where esal <(select max(esal) from emp where esal <(select max(esal) from emp)))))
1

Answer Wiki

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

Not knowing your tables, if they have the salary in them currently, just order by the salary in descending sequence, with fetch first 6 rows only (high to low). Then querey this result set in reverse order (low to high) with fetch first row only.

Discuss This Question: 3  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.
  • chowas400
    Hi Todd,
              Thanks for your response..

    I got your point but how to do it in single query?i mean how to use fetch in subquery? please advise..

    select *from emp order by esal desc
    fetch first 6 rows only           

    620 pointsBadges:
    report
  • hunshabbir7
    SELECT MIN(ESAL) FROM (SELECT * FROM EMP order by ESAL DESC FETCH FIRST 6 ROWS ONLY) AS TMP                                         
    2,900 pointsBadges:
    report
  • chowas400
    Hunshabbir,
                         Thank you very much ..
    620 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: