MS SQLServer and Oracle Take out the top N records sorted in a certain field
This topic looks like so simple, both databases provide the Order By clause. The problem should be able to solve it.
Try how to do MS SQLServer:
Use northwind;
CREATE TABLE TESTSORT (ID Integer);
INSERT INTO TESTSORT VALUES (3);
INSERT INTO TESTSORT VALUES (1);
INSERT INTO TESTSORT VALUES (4);
INSERT INTO TESTSORT VALUES (2);
Select * from tests.
-----------------------------------------
Id
-----------
3
1
4
2
(4 row (s) affected)
Suppose we have to take out the top three records sorted by ID:
SELECT TOP 3 * from testsort order by id;
-----------------------------------------
Id
-----------
1
2
3
(3 row (s) affected)
Very simple, a sentence is solved.
Try Oracle and use Oracle9i
SQL> CREATE TABLE TESTSORT (ID Number);
Table created.
SQL> INSERT INTO TESTSORT VALUES (3);
1 row created.
SQL> INSERT INTO TESTSORT VALUES (1);
1 row created.
SQL> INSERT INTO TESTSORT VALUES (4);
1 row created.
SQL> INSERT INTO TESTSORT VALUES (2);
1 row created.
SQL> commit;
COMMIT COMPLETE.
SQL> SELECT *.
Id
------------
3
1
4
2
Oracle does not have the top N-record top syntax in MS SQLServer. But ROWNUM can be used to complete similar functions.
SQL> Select * from test where rownum <= 3 ORDER BY ID;
Id
------------
1
3
4
Is it a bit unexpected? It does not return the result of the required 1, 2, 3. Oracle first select a range set (3, 1, 4) based on the condition limit of ROWNUM <= 3, and then in this collection Sort in.
The ORDER BY clause is only played after the appropriate record is taken out.
This is the case, then how can I achieve this function in Oracle?
Usually we can use this approach:
SQL> SELECT * FROM (Select * from testsort order by id) Where rownum <= 3;
Id
------------
1
2
3
It's a bit trouble, but it can only be like this.
The same reason, if you want to take out from the table, in accordance with a certain field, you are sorted by a record.
The following Oracle statement is the best:
SQL> SELECT ID from FROM
(
Select ID, Rownum As Con From
(
Select ID from testsort order by id
)
WHERE ROWNUM <= 3 / * n value * /
)
Where con> = 2; / * m value * /
Id
------------
2
3
This type of problem can also be solved with similar ideas in MS SQLServer.
Of course, you can also use a dummy approach, such as the collection of the previous M-1 records of the previous N-recorded min M-1 record collection. (A bit like a sign), but MS SQLServer seems to not support A collection of units such as Minus.
It seems that a small sorting problem is really not so simple!
Black_snail
Ligang1000@hotmail.com
Welcome to exchange, repost, please keep the above information
