sqlrpgle-6

285 pts.
Tags:
AS/400
SQLRPGLE
query to find n'th Highest salary in a file?

Software/Hardware used:
AS400(sqlrpgle)

Answer Wiki

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

N’th highest Salary in a file::

SELECT A.SAL FROM LIB/TESTPF A WHERE N-1 = (SELECT count(SAL)
FROM LIB/TESTPF WHERE a.sal<sal)

n=1,2,3,4,5,….

Discuss This Question: 8  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • ganeshpavi
    SELECT MAX(SALARY) FROM EMPLOYEE
    70 pointsBadges:
    report
  • shyedajmal
    I need 10'th highest salary ..in that case i need query...
    285 pointsBadges:
    report
  • philpl1jb
    I think you need a cursor .. here is a rough draft Declare cursor getIt as SELECT SALARY FROM EMPLOYEE order by SALARY DESC Then open the cursor Open getIt Then either fetch with loop do 10 fetch from getIt into Salary10 enddo
    49,850 pointsBadges:
    report
  • TomLiotta
    select min( SALARY ) from ( select EMPNO, SALARY from EMPLOYEE order by SALARY desc fetch first 10 rows only ) as t1 Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Great Tom
    49,850 pointsBadges:
    report
  • TomLiotta
    ...but of course there is a difference between the "10′th highest salary" and the "n'th Highest salary". By looking for the "nth" highest, there is a need for control by a variable value. That can make things very different. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    somehow I think that's going to need a bit of dynamic SQL
    49,850 pointsBadges:
    report
  • shyedajmal
    [...] TomLiotta and Philpl1jb explain how to use a query to find the 10th highest salary in a [...]
    0 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following