Simply put, Top N issues are: In SELECT, only the record of top n after sorting in a (some) column. Consider equivalence issues, can be divided into two: First, return N-N-record (M 1), the second is also a record (M 2) of the Nth-equivalent value (M 2). Of course, the innermost subquery can also have other clauses, or TOP N can also be applied without order BY. This is simpler.
1. SQL Server 7: Top N (with TIES)
M1:
SELECT TOP N * from myTable Order by ORD_COL;
M2:
SELECT TOP N ?? with ties * from myTable Order by ORD_COL;
Note: SQL Server 7 provides Percent n with Ties, and Top N is provided in Access, but the meaning is M 2.
2. Oracle 8i: Rownum <= n
M1:
SELECT *
????? (Select * from mytable order by order_col desc)
Where rownum <= n
M2:
Select * from myTable where order_col> =
??? (SELECT MIN "from FROM
???????? (Select * from myTable Order by ORD_COL DESC)
???? WHERE ROWNUM <= n)
Order by ORD_COL DESC
Note the following two wrong usage:
WRONG 1:
Select * from myTable
Where ?? rowid <= N
ORDER by ORD_COL DESC;
WRONG 2: (because where rownum <= N is executed before ordered)
Select * from myTable
WHERE?? Rownum <= n
ORDER by ORD_COL DESC;
3: DB2
Fetch first n rows only
M1:
Select * from myTable
Order by ORD_COL DESC
Fetch first n rows only
M2:
Did not find because DB2 is not allowed to nest in from from the ORDER BY clause.
It is not clear that Oracle's M 2 has a better way, and how other DBMs implement Top N operation, please add other friends.
-------------------------------------------------- --------------------------------
Each statement has been tested, Example:
Create Table MyTable (Mykey Int, ORD_COL INT);
INSERT INTO MyTable Values (1, 100);
INSERT INTO MyTable Values (2, 100);
INSERT INTO MyTable Values (3, 99);
INSERT INTO MyTable Values (4, 101);
INSERT INTO MyTable Values (5, 101);
