Discussion: SELECT top n Question (2)

select| problem Hi,everyone:

Today we continue to discuss select top N issues.
1. DB2 m 2
As mentioned in the previous discussion, M2 cannot be constructed because the DB2 subquery does not support an ORDER BY clause. In fact, DB2 provides a new set of functions, called OLAP functions, that can support top n issues very well.
M2:
SELECT * FROM
(SELECT Mykey,rank () over (Ord_col DESC) as Myrank
From MYTABLE) as Ranked_table
WHERE myrank<=n

With OLAP functions, in addition to the methods presented in the previous article, DB2 's M1 can be expressed as:
SELECT * FROM
(SELECT Mykey,row_number () over (Ord_col DESC) as Myrank
From MYTABLE) as Ranked_table
WHERE myrank<=n

2. In addition to M1, M2 two kinds of top N, there is also a DB2 called Dense_rank, (M3). explained separately by the following example.
M1:top 3--1 (4,101), 2 (5,101), 3 (1, 100) or 1 (4,101), 2 (5,101), 3 (2, 100)
M2:top 3--1 (4,101), 1 (5,101), 3 (1, 100), 3 (2, 100)
M3:top 3--1 (4,101), 1 (5,101), 2 (1, 100), 2 (2, 100), 3 (3,99)
In DB2, M3 is so implemented:
SELECT * FROM
(SELECT Mykey,dense_rank () over (Ord_col DESC) as Myrank
From MYTABLE) as Ranked_table
WHERE myrank<=n

3. In order to avoid DBMSs dialect problems, M2,M3 can be represented by the following criteria (SQL 92):
M2:
SELECT * from MYTABLE M1 WHERE n>
(SELECT COUNT (*) from MYTABLE M2
WHERE M2. Ord_col>m1. ORD_COL);
Note: Two comparison characters are >
M3:
SELECT * from MYTABLE M1 WHERE n>
(SELECT COUNT (DISTINCT M2.) Ord_col) from MYTABLE M2
WHERE M2. Ord_col>m1. ORD_COL);
Note: The difference with M2 is the use of distinct
As for M1, I have not yet thought of the appropriate standard wording, please add.

4. About MySQL
MySQL is simpler and more naïve than the mainstream RDBMS. When running MySQL, I found that subquery was not supported. So the top n problem in MySQL is a bit more complicated.
SELECT * from MYTABLE, MYTABLE M2
WHERE M2. Ord_col>=m1. Ord_col
GROUP by M1. MYKEY
Having COUNT (M2. Ord_col) <=n
However, there is a problem with this statement. An extreme example: when all the Ord_col values are the same. If you have any idea how to solve this problem, please add.

----------------------------------------------------------------------------------
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/26/2000