Discuss the SELECT TOP N problem_PHP tutorial

Hi, everyone:
This post discusses the SELECT TOP N issue.
I am currently working part-time in a computer company, developing ASP applications. In a recent business project, there was such a requirement: when querying, users only need to follow a certain The TOP 20 records after (some) column sorting. SQL SERVER 7 supports the TOP N operation very well, but considering the system transplantation problem, I also considered how to implement it in several other mainstream DBMSs. Since there is only DB2 UDB 7, ORACLE 8i, SQL SERVER 7, this post only discusses these three DBMS.
Simply put, the TOP N problem is: in SELECT, only select the TOP N records sorted by certain column(s). Taking into account the equivalent value The problem can be divided into two types: one is to return only N records (M 1), and the other is to also include all records with the same value as the Nth record (M 2). Of course, the innermost subquery can also have other The clause, or TOP N can also be applied without ORDER BY, which is simpler.
1. SQL SERVER 7: Use 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, ACCESS provides TOP N, but the meaning Yes M 2.
2. ORACLE 8i: Use 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
Pay attention to the following two incorrect usages:
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
use FETCH FIRST N ROWS ONLY
M1:
SELECT * FROM MYTABLE
ORDER BY ORD_COL DESC
FETCH FIRST N ROWS ONLY
M2:
not found because DB2 does not allow ORDER BY to be nested in FROM Subquery of the clause.
It is not clear whether there is a better way for ORACLE's M2, and how other DBMS implements TOP N operations. Please add other friends.

http://www.bkjia.com/PHPjc/631200.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631200.htmlTechArticleHi, everyone: This post discusses the SELECT TOP N issue. Now I am working part-time in a computer company, developing ASP applications. In There is such a requirement in a recent business project: when users query, they only need to...