×

Loading...

Topic

This topic has been archived. It cannot be replied.
  • 工作学习 / IT技术讨论 / here is the table, write a query to find out who have second highest salary? -interview(intervieweree); 2002-2-15 {499} (#369918@0)
    Name Salary
    -------------------------------------------------- -----------
    Bob 23456
    Sam 34567
    Bill 45678
    Green 12345
    Ken 12345
    Welly 59000

    (6 row(s) affected)
    • "Can you tell me who earns a little bit less than the top paid guy? " -hellangel(地狱天使); 2002-2-15 (#369958@0)
    • select * from name_salary where salary < (select max(salary) from name_salary) and rownum < 2; (FOR ORACLE) -bloor(不老); 2002-2-15 (#369965@0)
      • it's not correct -interview(intervieweree); 2002-2-15 {326} (#370411@0)
        your query equals to this one

        SELECT top 1 name, salary from employee WHERE salary NOT IN
        ( SELECT TOP 1 salary FROM employee ORDER BY salary DESC )
        order by salary desc

        but imagine if there are more than 1 people
        have the same salary which is second highest?
        your hardcoded number 2 does not work in thi situation
    • suppose the name of table is tn: -kiwi(kiwi); 2002-2-15 {143} (#369970@0)
      select name, salary from tn tn3
      where salary in
      (
      select max(salary) from tn t1
      where salary not in
      (
      select max(salary) from tn t2
      )
      )
    • select * from ( select name from [table name] order by salary desc ) where rownum = 2; -cloud2001(卷云溶月); 2002-2-15 (#370012@0)
    • My Answer (in MS SQL), similar like kiwi's -interview(intervieweree); 2002-2-15 {169} (#370304@0)
      select * from employee
      where salary=(
      select max(salary) from employee where
      salary<(select max(salary) from employee)
      )

      anybody knows answer in Informix or DB2?
枫下论坛主坛 /  工作学习 /  IT技术讨论