SQL Server queries the first and last records of the current record

Example:

If an employee table tb_user contains the field ID, name, and num (employee ID), the SQL statements of the former employee whose id = 20 is queried and the latter employee whose ID is 20 are:

Previous employee: Select top 1 * From tb_user where ID <20 order by id desc (with DESC, the database will start querying from the end of the table );

The next employee: Select top 1 * From tb_user where ID> 20 order by id asc (with ASC added, the database starts to query from the beginning of the table );

 

If you want to query by employee ID, for example, if you want to query the first employee and the last employee of an employee with employee ID 36, and the previous employee indicates that the employee ID is smaller than the current employee ID

The minimum number of employees, not the minimum number of employees with a smaller ID, and the next employee is the employee whose employee number is smaller than the current number. The SQL statement is as follows:

Previous employee: Select top 1 * From tb_user where num <36 order by num DESC;

Next employee: Select top 1 * From tb_user where num> 36 order by num ASC;

 

Of course, if you want to query the first record and the last record of the employee whose employee ID is 36 in the database (assume that the employee ID is 15) (the record in the database is handed by ID by default

In ascending order), the SQL text is as follows:

Previous employee: Select top 1 * From tb_user where ID <15 and num = 36 order by id desc;

Next employee: Select top 1 * From tb_user where ID> 15 and num = 36 order by id asc;

 

Similarly, to query the first 10 and the last 10 records of the current record, you only need to change select top 1 to select top 10.