Sort first or take first value

Sort
Sort first or take first value



Topic: Top N Records sorted by field in MS SQL Server and Oracle fetching tables



The topic seems so simple that both databases provide an ORDER BY clause. The problem should be solved.



First try what MS SQL Server does:



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 Testsort;

-----------------------------------------

Id

-----------

3

1

4

2

(4 row (s) affected)



Let's say we want to take out the first three records sorted by ID:



Select Top 3 * from Testsort order by ID;

-----------------------------------------

Id

-----------

1

2

3

(3 row (s) affected)



Very simple, a word will be solved.



Try Oracle again (here with 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 * from Testsort;

Id

----------

3

1

4

2



Oracle does not have top syntax for the first n records in MS SQL Server. But there are rownum that can be used to accomplish similar functions.



Sql> SELECT * from Testsort where rownum <= 3 order by ID;

Id

----------

1

3

4



Is the result a little unexpected? It does not return the required 1, 2, 3 results. Oracle first selects a range set (3,1,4) based on the rownum <=3 condition limit, and then sorts the collection.

The ORDER BY clause works after the appropriate records are removed.

So, how do you implement this functionality in Oracle?

Usually we can use this approach:

Sql> SELECT * FROM (SELECT * to Testsort ORDER by ID) where RowNum <=3;

Id

----------

1

2

3



It's a bit of a hassle, but it's the only way.



In the same way, if you want to get out of a table, sort the first m to N Records by a field

The following Oracle statements are the best:



Sql> Select ID 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



Similar ideas can be used in MS SQL Server to solve such problems.



Of course, you can also use a stupid way, such as using the first N records set minus the first M-1 records of the collection to get

The first m to N records collection. (a bit like a password), but MS SQL Server does not seem to support set operations such as minus.



It seems that a small sort of value problem is really not so simple!





Black_snail

Ligang1000@hotmail.com



Welcome to exchange, please keep the above information