n********6
发帖数: 1511 | 1 oracle 里面有TABLE: PAYROLL(ID, ..., Salary)
如果我
SELECT TOP 10 Salary
FROM PAYROLL
ORDER BY Salary Desc
能不能得到Salary最高的10条记录？
我记得TOP是随机读取的。是否有高人解释一下？ |
j*****n
发帖数: 1781 | 2 I thought TOP is only for T-SQL...
if it does, sure you will get the top 10 highest salary records, because the
result set is sorted. |
n********6
发帖数: 1511 | 3 Ok. Since top is not available in Oracle, let me do in this way.
1. SELECT *
INTO TempTable
FROM PAYROLL
WHERE Salary = (SELECT MAX(SALARY) FROM PAYROLL)
And ID NOT IN (SELECT ID FROM TempTable)
2. SELECT Count = COUNT(*) FROM TempTable
3. Loop 2.3., when Count > 10, exit |
B*****g
发帖数: 34098 | 4 http://www.bristle.com/Tips/SQL.htm#differences_between_oracle_and_ms_sql_server
临阵磨枪，不快也光。
【在 n********6 的大作中提到】
: Ok. Since top is not available in Oracle, let me do in this way.
: 1. SELECT *
: INTO TempTable
: FROM PAYROLL
: WHERE Salary = (SELECT MAX(SALARY) FROM PAYROLL)
: And ID NOT IN (SELECT ID FROM TempTable)
:
: 2. SELECT Count = COUNT(*) FROM TempTable
: 3. Loop 2.3., when Count > 10, exit
|
n********6
发帖数: 1511 | 5 Ok. Since top is not available in Oracle, let me do in this way.
1. CREATE TABLE TempTable
AS SELECT
FROM PAYROLL
WHERE Salary = (SELECT MAX(SALARY) FROM PAYROLL)
And ID NOT IN (SELECT ID FROM TempTable)
2. SELECT Count = COUNT(*) FROM TempTable
3. Loop 2.3., when Count > 10, exit |
c**t
发帖数: 2744 | 6 Oracle doesn't have top n query.
select x.salary
from (
select salary, rank() over (order by salary desc null last) as rnk fro
m payroll
) x
where x.rnk < 10;
or
select x.salary from (select salary from payroll order by salary desc)
where rownum < 10;
【在 n********6 的大作中提到】
: oracle 里面有TABLE: PAYROLL(ID, ..., Salary)
: 如果我
: SELECT TOP 10 Salary
: FROM PAYROLL
: ORDER BY Salary Desc
: 能不能得到Salary最高的10条记录？
: 我记得TOP是随机读取的。是否有高人解释一下？
|
