Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    2

    Unanswered: Can any body Help me in the Query

    Table Structure of Emp is
    EmpId int
    EName varchar
    DeptId int
    Sal Float

    Dept
    DeptNo int
    DName varchar


    I want to Find the second Highest Salary For Each Department

    Can any body help me !

    Regards
    Vishwas

  2. #2
    Join Date
    Jan 2003
    Location
    Vienna, Austria
    Posts
    102

    Re: Can any body Help me in the Query

    SELECT depname, salary
    FROM (
    SELECT rownum R, depname, salary
    FROM (
    SELECT d.dname depname , e.sal salary
    FROM emp e, dept d
    WHERE e.deptno = d.deptno
    ORDER BY e.sal desc
    )
    )
    WHERE R = 2;
    ^/\x

  3. #3
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107

    Re: Can any body Help me in the Query

    Originally posted by berxh3g
    SELECT depname, salary
    FROM (
    SELECT rownum R, depname, salary
    FROM (
    SELECT d.dname depname , e.sal salary
    FROM emp e, dept d
    WHERE e.deptno = d.deptno
    ORDER BY e.sal desc
    )
    )
    WHERE R = 2;


    Hi,
    Try This SQL
    This SQL gives the second highest salary department wise.
    SELECT d.dname depname, max(e.sal) salary
    FROM emp e, dept d
    WHERE e.deptno = d.deptno
    AND sal != (SELECT MAX(sal) FROM emp x WHERE x.deptno = e.deptno)
    GROUP BY d.dname
    ORDER BY 1,2
    Last edited by NoviceNo1; 02-03-03 at 05:42.
    nn

  4. #4
    Join Date
    Jan 2003
    Location
    Vienna, Austria
    Posts
    102

    Re: Can any body Help me in the Query

    you're right,
    I didn't read the whole post. sorry.
    (shouldn't post anything on monday morning)
    ^/\x

Posting Permissions

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