Summary of common methods for SQL Server to query the first N records, SQL Server

Summary of common methods for SQL Server to query the first N records, SQL Server

This article describes the common methods for SQL Server to query the first N records. Share it with you for your reference. The details are as follows:

The first N records of SQL Server query are frequently used. The method for querying the first N records of SQL Server is described in detail. If you are interested, take a look.

SQL Server queries the first N records:

Because IDs may not be consecutive, you cannot obtain 10 <id <20 records.

There are three methods to achieve this:

1. Search for the first 20 records, excluding the first 10 records

Statement:
Copy codeThe Code is as follows: select top 20 * from tbl where id not in (select top 10 id from tbl)
2. Generate a temporary table by searching records and create an auto-incremental id for the temporary table. Obtain the required data using the 10 <id <20 Record of the auto-increment id
Statement:
Copy codeThe Code is as follows: select identity (int, 1, 1) as id, * into # temp from tbl;
Select * from # temp where id between 10 and 20

The second method is actually two statements, but you can let him execute them consecutively, just like a statement to complete the task.

3. My friends think that the first method is too inefficient. After discussion, the third method is obtained:
Statement:
Copy codeThe Code is as follows: select top 10 * FROM (select top 20 * FROM tblORDER BY id) as tbl2 order by tbl2.id DESC

I hope this article will help you design SQL Server database programs.