Remove the first N records sorted by a field from the MS sqlserver and Oracle

MSSqlserverAndOracleBeforeNRecords

This question seems so simple. Both databases provide order by clauses. The problem should be solved. Try Ms first. SqlserverHow to do this: 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 ----------- 3142 (4 row (s) affected) Suppose we want to retrieve the first three records sorted by ID: SelectTop 3 * From testsort order by ID; --------------------------------------------- ID ----------- 123 (3 row (s) affected) is very simple and solved in one sentence. try Oracle (Oracle9i here) 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 no Ms SqlserverThe top syntax of the First N records. RownumCan be used to complete 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 results of 1, 2, 3. Oracle first according Rownum<= 3: select a range set (3, 1, 4), and then sort it in the set. the order by clause takes effect only after an appropriate record is taken out. in the past, how can this function be implemented in Oracle? We can usually use this method: SQL>Select* From (Select* From testsort order by ID) WhereRownum<= 3;Id ---------- 1 2 3 is a little troublesome, but it is only possible. in the same way, it is best to retrieve the Oracle statements from the table from m to N records before sorting by a field: SQL> SelectID from ( SelectID, RownumAs con from ( SelectID from testsort order by ID) WhereRownum<= 3/* n Value */) Where con> = 2;/* m Value */Id ---------- 2 3 MS SqlserverSimilar ideas can also be used to solve such problems. of course you can also use a stupid way, such as using the collection of the First N records minus before the collection of M-1 records to get the first m to N records set. (a bit like a Rao password), But Ms SqlserverIt seems that minus and other collection operations are not supported. It seems that a small sorting value is really not that simple! Black_snil Ligang1000@hotmail.com welcome to exchange, paste please keep the above information trackback: http://tb.blog.csdn.net/TrackBack.aspx? Postid = 98363