FirebirdSQL logo

Examples of FIRST/SKIP

  1. The following query will return the first 10 names from the People table:

    select first 10 id, name from People
      order by name asc
  2. The following query will return everything but the first 10 names:

    select skip 10 id, name from People
      order by name asc
  3. And this one returns the last 10 rows.Notice the double parentheses:

    select skip ((select count(*) - 10 from People))
      id, name from People
      order by name asc
  4. This query returns rows 81 to 100 of the People table:

    select first 20 skip 80 id, name from People
      order by name asc

ORDER BY

When a SELECT statement is executed, the result set is not sorted in any way.It often happens that rows appear to be sorted chronologically, simply because they are returned in the same order they were added to the table by INSERT statements.This is not something you should rely on: the order may change depending on the plan or updates to rows, etc.To specify an explicit sorting order for the set specification, an ORDER BY clause is used.

Syntax
SELECT ... FROM ...
...
ORDER BY <sort-specification [, <sort-specification> ...]

<sort-specification> ::=
  <value-expression> [<ordering-specification>] [<null-ordering>]

<ordering-specification> ::=
    ASC  | ASCENDING
  | DESC | DESCENDING

<null-ordering> ::=
    NULLS FIRST
  | NULLS LAST
Table 1. Arguments for the ORDER BY Clause
Argument Description

value-expression

Any expression;an expression that is only an integer literal represents the column position

The ORDER BY consists of a comma-separated list of the columns or expressions on which the result data set should be sorted.The sort order can be specified by the name of the column — but only if the column was not previously aliased in the SELECT columns list.The alias must be used if it was used in the select list.The ordinal position number of the column in the SELECT column list, the alias given to the column in the SELECT list with the help of the AS keyword, or the number of the column in the SELECT list can be used without restriction.

The three forms of expressing the columns for the sort order can be mixed in the same ORDER BY clause.For instance, one column in the list can be specified by its name and another column can be specified by its number.

Important

If you sort by column position or alias, then the expression corresponding to this position (alias) will be copied from the SELECT list.This also applies to subqueries, thus, the subquery will be executed at least twice.

Note

If you use the column position to specify the sort order for a query of the SELECT * style, the server expands the asterisk to the full column list to determine the columns for the sort.It is, however, considered “sloppy practice” to design ordered sets this way.

Sorting Direction

The keyword ASC — short for ASCENDING — specifies a sort direction from lowest to highest.ASC is the default sort direction.

The keyword DESC — short for DESCENDING — specifies a sort direction from highest to lowest.

Specifying ascending order for one column and descending order for another is allowed.

Collation Order

Using the keyword COLLATE in a <value-expression> specifies the collation order to apply for a string column if you need a collation order that is different from the normal collation for this column.The normal collation order is defined by either the default collation for the database character set, or the collation set explicitly in the column’s definition.