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?Name Salary
-------------------------------------------------- -----------
Bob 23456
Sam 34567
Bill 45678
Green 12345
Ken 12345
Welly 59000
(6 row(s) affected)
-interview(intervieweree);
2002-2-15
{499}
(#369918@0)
-
"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 correctyour 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
-interview(intervieweree);
2002-2-15
{326}
(#370411@0)
-
suppose the name of table is tn: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
)
)
-kiwi(kiwi);
2002-2-15
{143}
(#369970@0)
-
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'sselect * from employee
where salary=(
select max(salary) from employee where
salary<(select max(salary) from employee)
)
anybody knows answer in Informix or DB2?
-interview(intervieweree);
2002-2-15
{169}
(#370304@0)