Results 1 to 12 of 12
  1. #1
    Join Date
    May 2010
    Location
    Hyderabad, India
    Posts
    16

    Unanswered: How to fetch the TOP second row from a table

    Here i have employee, dept tables.

    I have to fetch the emp_id who is earning 2nd highest salary in SALES department.


    table data as follows:.

    EMPLOYEE table data:

    EMP_ID DEPT_ID SALARY
    ----------- ----------- -----------
    1 1001 25000
    2 1001 26000
    3 1001 27000
    4 1001 28000
    5 1002 29000
    6 1002 20000
    7 1002 35000
    8 1002 40000
    9 1002 60000
    10 1003 26000
    11 1003 75000
    12 1003 15000

    Departments Table Data:

    DEPT_ID DEPT_NAME
    ----------- ---------
    1001 PRODUCTION
    1002 DESIGN
    1003 SALES

    I need to fetch Employee Id 10 in a single query.

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Use a select with order by salary (descending) as input for a cursor and take the values of the second fetch?
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    May 2010
    Location
    Hyderabad, India
    Posts
    16
    i want to fetch it in a single select query.

    Is it possible ??

  4. #4
    Join Date
    May 2010
    Location
    Hyderabad, India
    Posts
    16
    Is there any alternative other than below query.

    SELECT EMP_ID, SALARY FROM
    (
    SELECT EMP.* FROM EMPLOYEE EMP,
    DEPT DEPT
    WHERE EMP.DEPT_ID = DEPT.DEPT_ID
    AND DEPT.DEPT_NAME = 'SALES'
    GROUP BY EMP.DEPT_ID
    HAVING EMP.SALARY <> MAX(EMP.SALARY)
    ) P
    GROUP BY P.DEPT_ID
    HAVING P.SALARY = MAX(P.SALARY)

  5. #5
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Looks good to me. Plus, it technically is one query too .
    There aren't ways to determine the nth record in a resultset.
    I'm not crazy, I'm an aeroplane!

  6. #6
    Join Date
    Jun 2010
    Posts
    51
    Try this.

    set rowcount 2

    declare @h_salary numeric(15,3)

    select @h_salary=salary
    from EMPLOYEE e, department d
    where e.dept_id and d.dept_id
    and dept_name="sales"
    order by desc

    select @h_salary

  7. #7
    Join Date
    May 2010
    Location
    Hyderabad, India
    Posts
    16
    this is not a single query and more over

    How can we store two values in @h_salary varibale.

    Below query will fetch the top 2 salaries

    select salary
    from EMPLOYEE e, department d
    where e.dept_id and d.dept_id
    and dept_name="sales"
    order by desc
    -PavanKumar M Reddy

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by reddy_546 View Post
    Below query will fetch the top 2 salaries
    no it won't, that query has several glaring syntax errors

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2010
    Posts
    9
    i think this will work

    select * from tableName
    where
    salary not in (select max(salary) from tableName)
    order by salary desc

    ..

    you have to select top in this salary.. so may be top(1) will work out
    i have not cared about syntax of sql, i am just giving logic
    please correct me if i m wrong

  10. #10
    Join Date
    Jun 2010
    Posts
    51
    How can we store two values in @h_salary variable.
    We can't, I thought we only want 2nd highest salary/id to be extracted and not top 2. This query utilizes this idea only. May not be a single query but surely is fastest one.

    no it won't, that query has several glaring syntax errors
    I could only find a couple. Sorry for not testing in properly. But if one gets the idea of what is being done then anyone can solve the problem. Anyway..below is the correct query:

    Code:
    set rowcount 2
    
    declare @emp_id int
    
    select @emp_id=emp_id
    from emp e, dept d
    where e.dept_id = d.dept_id
    and dept_name="sales"
    order by salary desc
    
    select @emp_id
    I originally intended to show 2nd highest salary..but this will show emp_id.

  11. #11
    Join Date
    Aug 2010
    Posts
    19
    select max(salery)
    from t1
    where salery < (select max(salery) from t1)

    in other words, select the max salery which is smaller than the absolut max salery :-)

  12. #12
    Join Date
    Jun 2010
    Posts
    9
    this query even i thought will work but it seems to be wrong..
    what if two employees have same salaray which comes in TOP salary.
    if this is allowed then yes ur answer is right and is also fine from interview point of view
    better select count(*) for max salary first then appropriately make decision

Posting Permissions

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