Examples Of FIRST/SKIP

CommentsProcedural SQL (PSQL) Statements

Examples of FIRST/SKIP

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

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

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.