r********g
发帖数: 868 | 1 How will u find the highest and second highest salary in each group using a
single SQL query ?
have to write SQL code used in different platforms: e.g. both Oracle and SQL
Server |
a*****e
发帖数: 21 | 2 select MAX(t1.salary), MAX(t2.salary), group
from table t1, table t2
where t1.group = t2.group and t1.salary > t2.salary
group by t1.group
就是不知道group by能不能这么用...
a
SQL
【在 r********g 的大作中提到】
: How will u find the highest and second highest salary in each group using a
: single SQL query ?
: have to write SQL code used in different platforms: e.g. both Oracle and SQL
: Server
|
r********t
发帖数: 395 | 3
almost correct.但是既然t1和t2都要在每个group里比较，那么需要group by两个：
测试过了。。。
【在 a*****e 的大作中提到】
: select MAX(t1.salary), MAX(t2.salary), group
: from table t1, table t2
: where t1.group = t2.group and t1.salary > t2.salary
: group by t1.group
: 就是不知道group by能不能这么用...
:
: a
: SQL
|
x***y
发帖数: 633 | 4 great, but need some minor modifications to the condition as
" from table t1, table t2
on t1.group = t2.group
where t1.salary >= t2.salary && t1.primaryKey != t2.primaryKey"
when considering 2 people with the same salary.
on t1.group = t2.group
where t1.salary >= t2.salary && t1.primaryKey != t2.primaryKey
【在 a*****e 的大作中提到】
: select MAX(t1.salary), MAX(t2.salary), group
: from table t1, table t2
: where t1.group = t2.group and t1.salary > t2.salary
: group by t1.group
: 就是不知道group by能不能这么用...
:
: a
: SQL
|
c******e
发帖数: 73 | 5 using rank function in oracle? |
y*********e
发帖数: 518 | 6 不用GROUP BY。
找第二高的：
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees)
或者：
SELECT MIN(salary) FROM (
SELECT salary FROM employees ORDER BY salary RANGE 1 TO 2
) AS TEMP_TABLE
找第三高的以此类推。
执行的效率？若是salary上有B+ tree index，那么效率是O(logn)。
a
SQL
【在 r********g 的大作中提到】
: How will u find the highest and second highest salary in each group using a
: single SQL query ?
: have to write SQL code used in different platforms: e.g. both Oracle and SQL
: Server
|
