ORDER BY Clause (SQL Server Compact)

Specifies the sort order for the result set. The ORDER BY clause is not valid in subqueries. You also have an option to fetch only a window or page of results from the resultset using OFFSET-FETCH clause.

Syntax

[ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n][<offset_fetch>] ] 

<offset_fetch> ::= {OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }    [FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY    ]}

Arguments

  • order_by_expression
    Specifies a column on which to sort. A sort column can be specified as a name or column alias, which can be qualified by the table name, or an expression. Multiple sort columns can be specified. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set.

    The ORDER BY clause can include items not appearing in the select list.

    Note

    Columns of data type ntext and image cannot be used in an ORDER BY clause.

  • ASC
    Specifies that the values in the specified column should be sorted in ascending order, from lowest value to highest value.

  • DESC
    Specifies that the values in the specified column should be sorted in descending order, from highest value to lowest value. Null values are treated as the lowest possible values.

  • OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
    Specifies the number of rows to skip, before starting to return rows from the query expression. The argument for the OFFSET clause can be an integer or expression that is greater than or equal to zero. You can use ROW and ROWS interchangeably.

  • FETCH {FIRST|NEXT} <rowcount expression> {ROW|ROWS} ONLY
    Specifies the number of rows to return, after processing the OFFSET clause. The argument for the FETCH clause can be an integer or expression that is greater than or equal to one. You can use ROW and ROWS interchangeably. Similarly, FIRST and NEXT can be used interchangeably.

Remarks

There is no limit to the number of items in the ORDER BY clause.

If the ORDER BY clause is used with a UNION statement, then the columns on which you sort must be the column names or aliases specified in the first SELECT statement. For example, the first of the following SELECT statement succeeds, while the second fails.

This statement succeeds because col1 belongs to the first table (t1)

Create t1 (col1 int, col2 int);

Create t2 (col3 int, col4 int);

SELECT * from t1 UNION SELECT * from t2 ORDER BY col1;

This statement fails because col3 does not belong to the first table (t1)

SELECT * from t1 UNION SELECT * from t2 ORDER BY col3;

Example

The following example lists employees by their first names.

SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name

The following examples demonstrate the use of OFFSET-FETCH clause with ORDER BY, where skipping first 10 rows from the sorted resultset and returning the remaining rows.

SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS;

See Also

Reference

OFFSET FETCH Clause (SQL Server Compact)