SQL statement "Remove 10th through 20th records from datasheet" +select Top usage method

1 . First. How to use SELECT top:

Reference questions select Top N * FROM and select * from differences

Select * FROM table-fetch all data. Return unordered collection

Select Top N * from Table--Returns the unordered collection by taking the first n from the data storage order in the table

Select *  from TableOrder  by IDdesc--Take all the data. Returns an ordered list in reverse order by ID

Select Top N * from TableOrder  by IDdesc--Reverse by ID first. Fetch the first n and return an ordered set sorted by IDnote, sort by an attribute. The data column value of the sort attribute is best not repeated. Assume that there are repeated. Those rows in the result set that have equal values for the sort attribute are not predetermined in advance. "

chestnuts such as the following ~

We use the PID as the sorting attribute value, the 16th row, the 19th line and the 20th line PID values are equal.

Now take the countdown to the PID sort of 5 records:

Connection con=new SQLConnection (). getconnection ();
String sql= "SELECT Top 5 * from Test ORDER by pid DESC";

System.out.println ("Select Begins ...");

Statement statement=con.createstatement ();
ResultSet result = statement.executequery (SQL);
while (Result.next ()) {
System.out.println (Result.getint (1) + "," +result.getstring (2) + "," +result.getstring (3));
}
System.out.println ("Select Ends ...");
Con.close ();
Statement.close ();
Result.close ();
Con=null;
Statement=null;
Result=null;

Results:

Select Begins ...
3,as,9
16,tt,8 "Three orders in advance cannot be determined"
19,gh,8
20,jk,8
6,bb,7
Select Ends ...

2. SQL statement similar to "query 10th to 20th record" = = = is often applied to paginated display
1) String sql= "SELECT Top Ten * FROM (SELECT * from Test where id<21) m order by m.id desc"; Note The ID is the primary key. Subquery takes out the first 20 records, the main query first descending and then fetch the first 10. But the result is descending. So be careful when dealing with it.
2) Query section m to article N records:
String sql= "SELECT Top n-m+1 * from Test where (the ID not in (the select top m-1 ID from test))"; Can be the normal sequence of article m to nth record writing. Very recommended!
3) " There are some minor problems. I don't know where it went wrong, write it. If there is a spectator know please leave a message oh ~ " String sql = "SELECT Top ten * FROM (select top of * from Test) a order by a.id Desc"; try the data in the table above, the result is:"Why is it from 12th to 21st?" Don't want to be clear. " kl,100,
jk,8,
gh,8,
aas,18,
, qw,19
tt,8,
ww,15,
hh,13,
gg,16,
ui,11,

Doubts about 3). In the blog park to find such an article "to talk about SQL database" Simple "Select top-may have you never notice the details of

-------------------------------Reference starts-----------------------------------

Data tables such as the following:

ID EMPNO NAME Age

1 26929 Jerome 28
2 28394 Quince 27
3 20983 Green 30
4 27189 Mike 30
5 23167 arishy 30
6 26371 Yager 29

I wrote the SQL statement want to get 3rd, 4 data, test paging play.

Select Top 2 * FROM (select top 4 * from Member ) m ORDER BY M.rowid Desc

I run the middle section subquery:select top 4 * from Member

The results are:

1 26929 Jerome 28
2 28394 Quince 27
3 20983 Green 30
4 27189 Mike 30

The result of the entire SQL statement, however, is: "You have actually encountered this problem." But I don't know why .... "

5 23167 arishy 30
6 26371 Yager 29

Select Top 2 * FROM (SELECT top 4 * from table) m ORDER BY m.id DESC-----Scan table after the first descending and then in 4 rows to take 2 lines "a bit of doubt, not scan table--take 4 lines-descending--Take 2 lines??" "

Select top 2 * FROM [select top 4 * FROM table ORDER by ID ASC] m ORDER BY m.id DESC-----After scanning the table, take 4 rows in ascending order and then take the 4 rows in descending line 2

The problem involves subqueries in SQL:

out of the table in the FROM clause we call the derived table. derived tables are virtual and are not physically detailed. This means that when compiling

the time. such as (select Top 2 * FROM (SELECT top 4 * from table) m order by M.id

desc), external queries and internal queries are merged and a plan is generated.

(Note: in a derived table, you generally do not agree to use ORDER by unless you specify top.) which means select top

2 * FROM (SELECT * to Zhuisuo ORDER by ID ASC) m ORDER BY m.id Desc This sentence is not

can be run).

A derived table is a virtual table to be externally referenced. the order by returns not a table but a cursor . So the only order by is limited. But why is it possible to use top plus order by? is because top can select a specified number from the cursor returned by the order by to generate a table and return.

Another example of the top need to pay attention to the details

1. Using top to return random rows, very many people would think of using the RAND function to get such a statement

Select top 4 id,name from Table order by Rand ();

After many queries, you will be disappointed to find that it does not return random rows. This is because every query only calls it once instead of every

Row to call it once.

2. Note that using top in insert, the correct flashback insert Top method should be:

INSERT INTO table

Select TOP (4) * FROM table ORDER BY id DESC

------------------------------End of reference----------------------------

See the original blog, about the top of the details, or do not make it clear, and then more to see-more practice and summed up

SQL statement "Remove 10th through 20th records from datasheet" +select Top usage method