1. First, select top usage:
Reference question the difference between select top N * FROM and select * FROM
Select * from table--Fetch all data, return unordered collection Select top n * from table -Returns an unordered collection based on the first n of the data storage order in the table
Select * fromTableOrder byIDdesc--Take all the data and return the ordered list in reverse order of IDSelectTopN * from TableOrder byIDdesc--Reverse the first order of IDs, then fetch the first n, and return an ordered set sorted by ID.
Note that sorting by an attribute is preferable to the data column value of the sort attribute, and if there are duplicates, the order of those rows with equal values for the sort attribute is not predetermined in the result set beforehand .chestnuts are as follows ~
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 5 record:
<span style= "FONT-SIZE:18PX;" >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 ()) { </span>
<span style= "FONT-SIZE:18PX;" > 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;</span>
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 * From (SELECT * FROM Test where id<21) m o rder by m.id desc "; Note that the ID is the primary key, the subquery takes out the first 20 records, the main query first descending and then fetch the first 10, But the result is descending, so the subsequent processing should pay attention to 2) Query article M to nth record:
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 Oh ~ 3) "There are some small problems, I do not know where the wrong, write, if there is a spectator know please leave a message!" 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?" I don't understand. kl,100,
jk,8,
gh,8,
aas,18,
, qw,19
tt,8,
ww,15,
hh,13,
gg,16,
ui,11,
SQL statement "Remove 10th through 20th records from datasheet" +select top usage