ORDER BY

The ORDER BY clause is used to sort the returned results.

note

If no ORDER BY clause is specified, then the query result doesn’t have any default ordering.

The syntax starts with keyword ORDER BY followed by one or more sorting expressions. A sorting expression consists of an identified path, optionally followed by one of the DESC, DESCENDING, ASC, or ASCENDING keyword, indicating the sorting type (descending or ascending), e.g.

ORDER BY c/name/value DESC

Sorting rows assumes that data identified by the path (from the sorting expression) are comparable. It implies the use of a specific operators like equal, less-than and greater-than (all available to primitives and Ordered types) on data identified by path.

When sorting type is not specified, ASC (ascending) is assumed by default.

Multiple sorting expressions are separated using a comma. If two rows are equal according to the leftmost expression, they are compared according to the next sorting expression.

Example

SELECT
   obs/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/magnitude AS systolic,
   obs/data[at0001]/events[at0006]/data[at0003]/items[at0005]/value/magnitude AS diastlic,
   c/context/start_time AS date_time
FROM
   EHR e[ehr_id/value=$ehrUid] CONTAINS 
    COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v1]
      CONTAINS OBSERVATION obs [openEHR-EHR-OBSERVATION.blood_pressure.v1]
WHERE
   obs/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/magnitude >= 140 OR
   obs/data[at0001]/events[at0006]/data[at0003]/items[at0005]/value/magnitude >= 90
ORDER BY
   c/context/start_time DESC

LIMIT

The LIMIT clause is used to constrain the result set generated by the rest of the query. It is often used together with an OFFSET subclause.

The OFFSET specifies the number of rows to skip before it starts to return rows from the query. The LIMIT specifies the number of rows to return after the OFFSET clause has been processed. Note that when using LIMIT, deterministic behavior requires that the ORDER BY clause is also used to constrain the result in a unique order.

Where DISTINCT modifier is used the LIMIT and OFFSET applies to remaining rows, after duplicates were filtered out from result set.

The syntax was borrowed from SQL language, similar to PostgreSQL and MySQL implementation. It consists of two parts: keyword LIMIT followed by number, optionally followed by OFFSET followed by a number:

LIMIT row_count [OFFSET offset]

Both row_count and offset are integer numbers, row_count minimal value is 1, while minimal value for offset is 0.

If a LIMIT row_count is given, no more than that many rows will be returned (but possibly fewer, if the query itself yields fewer rows). The OFFSET offset is optional, and when not specified then offset = 0 is assumed.

The LIMIT row_count OFFSET offset is used to get results in a paginated way. For instance, when offset = 0 and row_count = 10, the result will have at most 10 rows, starting with first row. The offset of the first row is 0 (not 1). Then with offset = 10 and row_count = 10, the result will contain next 10 rows at most (i.e. rows 11-20 respectively). So increasing offset by row_count, allows to get all the results in a paginated way.

The following example will return 10 rows, representing the second page of result set, ordered by event start time:

SELECT
   c/name/value AS Name, c/context/start_time AS date_time, c/composer/name AS Composer
FROM
   EHR e[ehr_id/value=$ehrUid]
      CONTAINS COMPOSITION c
ORDER BY c/context/start_time
LIMIT 10 OFFSET 10