SQL Server record filtering (top, ties, offset) rollup

First, TOP screening

If there is an ORDER BY clause, the TOP filter returns the specified number of rows based on the sorted result. If there is no ORDER BY clause, the TOP filter returns the specified number of rows in the physical order of the rows.

1. Returns the specified number of rows

TOP is used to indicate that a specified number of rows are returned from the query result set.

For example, return the first 2 rows of records

SELECT TOP (2) ColumnA, Columnbfrom Table1

　　

2. Returns the specified percentage of rows

Percentages can be used, and if the result of a percentage calculation is not an integer, rounding up (that is, "go to" instead of "round" or "truncate rounding"). For example, return the first 10% rows:

SELECT TOP (PERCENT ColumnA, Columnbfrom Table)

　　

3.withTIES parameters

When used in combination with the ORDER BY clause, there are sometimes side-by-side rankings, for example, students returning to the top 10 outstanding grades may encounter multiple students in 10th place. At this point, you need to specify with TIES to ensure that students in 10th place are included in the result set, which may have more than 10 rows. Example:

SELECT TOP (Ten) with TIES ColumnA, Columnbfrom table1order by ColumnA DESC

　　

Second, OFFSET screening

The OFFSET clause must be used in combination with the ORDER by clause and cannot be used with TOP. OFFSET does not have a PERCENT parameter or a with TIES parameter compared to TOP.

1. Skip the specified number of rows

The OFFSET clause specifies the number of rows that will be skipped before the rows are returned from the query expression. The argument to the OFFSET clause can be an integer or an expression that is greater than or equal to zero. ROW and ROWS can be used interchangeably. For example:

SELECT ColumnA, Columnbfrom table1order by Columnaoffset ROWS

　　

2. Skips the specified number of rows and returns the specified number of rows

The FETCH clause cannot be used alone, and must follow the OFFSET clause.

The FETCH clause specifies the number of rows that will be returned after the OFFSET clause is processed. The parameter of a FETCH clause can be an integer or an expression that is greater than or equal to 1. For example:

SELECT ColumnA, Columnbfrom table1order by Columnaoffset rowsfetch NEXT 5 ROWS only

　　

3. Parameter Interchange

(1) ROW and ROWS can be used interchangeably

The expression "1 ROWS", although SQL Server syntax, does not conform to English syntax, so row and ROWS can be interchanged, such as "1 row".

(2) First and NEXT can be used interchangeably

When "OFFSET 0 rows" is encountered (that is, no rows are skipped), the "Fetch NEXT 5 rows Only" statement does not look natural, so you can change to "fetch first 5 rows only".

4. Expression of the number of rows

The number of rows can use any arithmetic, constant, or argument expression that returns an integer value, but you cannot use a scalar subquery.

SQL Server record filtering (top, ties, offset) rollup