Discussion: SELECT Top N problem

Select| questions Hi, everyone:
This post discusses select top N issues.
is now working part-time in a computer company to develop ASP applications. In a recent business project there is a need for users to query, only to be sorted by a certain column after the top 20 records. SQL SERVER 7 is a good support for top N operations, but given the problem of system porting, I also consider how to implement it in several other mainstream DBMSs. Given that only DB2 UDB 7,oracle 8i,sql SERVER 7, this post discusses only these three DBMS.
Simply put, the top n problem is: In Select, select only the records of top n that are sorted by some columns. Considering the equivalence problem, it can be divided into two kinds: one is to return only N records (M 1), and the second is to include all records (M 2) equivalent to Nth. Of course, the most inner subqueries can have other clauses, or top n can be applied without an order by, which 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, where top n is provided in ACCESS, but the meaning is m 2.

2. ORACLE 8i: With Rownum<=n
M1:
SELECT * FROM
(SELECT * from MYTABLE ORDER by Ord_col DESC)
WHERE rownum<=n
M2:
SELECT * from MYTABLE WHERE ord_col>=
(SELECT MIN (Ord_col) from
(SELECT * from MYTABLE ORDER by Ord_col DESC)
WHERE rownum<=n)
ORDER BY Ord_col DESC
Note the following two types of error usage:
Wrong 1:
SELECT * from MYTABLE
WHERE rowid<=n
Order by Ord_col DESC;

Wrong 2: (because where rownum<=n is executed before order by)
SELECT * from MYTABLE
WHERE rownum<=n
Order by Ord_col DESC;

3:db2
With the fetch of ROWS only
M1:
SELECT * from MYTABLE
ORDER BY Ord_col DESC
FETCH a N ROWS only
M2:
Not found because DB2 does not allow subqueries nested with an ORDER BY clause in from.

It's not clear if Oracle's M 2 has a better idea, and how other DBMS can implement top n operations and ask other friends to add it.
----------------------------------------------------------------------------------
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);

Fang 6/23/2000