This topic discusses SELECTTOPN. Hi, everyone: this post discusses the SELECTTOPN issue. I am now working in a computer company to develop ASP applications. in a recent commercial project, users only need Hi, everyone:
This post discusses the select top n issue.
I am now working in a computer company to develop ASP applications. in a recent commercial project, a user needs to sort the TOP 20 records by certain columns. SQL server 7 supports the top n operations, but considering the system porting problem, I also consider how to implement it in several other mainstream DBMSs. in view of the fact that only DB2 UDB 7, ORACLE 8i, and SQL SERVER 7 are available, this post only discusses these three DBMS.
To put it simply, the top n problem is: in the SELECT statement, only the top n records are sorted by certain columns. considering the equivalence problem, there are two types: one is to return only N records (M 1), and the other is to include all records equivalent to N records (M 2 ). of course, the inmost subquery can also have other clauses, or the top n can also be applied without 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, and ACCESS provides TOP N, which means 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
Note the following two usage errors:
WRONG 1:
SELECT * FROM MYTABLE
Where rowid <= N
Order by ORD_COL DESC;
WRONG 2 :( because where rownum <= N is executed before order)
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:
Not found, because DB2 cannot nest subqueries with order by clauses in FROM.
It is unclear whether oracle m 2 has a better solution and how other DBMS can implement top n operations.
Http://www.bkjia.com/PHPjc/631200.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/631200.htmlTechArticleHi, everyone: this post discusses the select top n problem. I am now working in a computer company to develop ASP applications. in a recent commercial project, there is a need that users only need...
