Results 1 to 3 of 3

Thread: Find Max(sal)

  1. #1
    Join Date
    Sep 2006
    Posts
    8

    Question Unanswered: Find Max(sal)

    Hi All,

    I have a problem : We have one table EMP with columns Eid, Ename,Salary .

    now i want details of an employee who has 2nd maximum salary . (only 2nd max details row ) and 3rd max salary .

    In both cases we have to display single row (corresponding salary row) only

    Please help me.

    Thanks advance

  2. #2
    Join Date
    Nov 2006
    Posts
    13
    --second highest salary
    select max(sal) from #t a
    where sal != (select sal from #t b
    having max(sal) = sal)




    --Third highest
    select max(sal) from #t
    where sal !=
    (
    select sal from #t where sal =
    (
    select max(sal) from #t a
    where sal != (select sal from #t b
    having max(sal) = sal)
    )
    ) and sal != (select sal from #t b
    having max(sal) = sal)

  3. #3
    Join Date
    Nov 2006
    Posts
    13
    Here is another easy way for finding the 2nd,3rd,4th and so on value.

    select First=max(e.sal),Second=max(f.sal),Third=max(t.sal ),fourth=max(r.sal) from #w e , #w f, #w t,#w r
    where e.sal > f.sal
    and f.sal > t.sal
    and t.sal > r.sal

    --Asit

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •