DISTINCT

Collapse repeated rows so each value appears once.

Syntax

DISTINCT has two forms, one for each place it is written.

In a LOOKUP, it is a function of the call, written with brackets:

DISTINCT()
DISTINCT("column", …)

After a GET, and in a FOREACH body, it is a keyword with no brackets. The columns follow it, separated by commas, up to the next clause:

DISTINCT
DISTINCT alias.column, …

Parameters and arguments

  • none

    With no column, DISTINCT removes repeated rows. In a LOOKUP the rows are the columns SHOW lists; after a GET they are whole rows

    Required: No
  • "column"

    In a LOOKUP, a column written as quoted text, or table.column for a joined table. DISTINCT keeps one whole row for each value of these columns

    Required: No
  • alias.column

    After a GET or in a FOREACH body, a column written plainly, by the alias of the read or bare. Several are separated by commas, and none is quoted or bracketed

    Required: No

Examples

DISTINCT() with no columns removes repeated rows of the shown column.

Return type and behaviour

DISTINCT returns nothing itself. It reduces the rows of the statement it belongs to.

No column. In a LOOKUP the result is SELECT DISTINCT over the columns SHOW lists, so a row is removed only when every shown column repeats. After a GET, the database reads whole rows with SELECT DISTINCT; when the table has a key such as id, every row differs and nothing is removed, so name a column. After a RETURN, a bare DISTINCT compares each row's answer and drops repeated answers.

With columns. DISTINCT keeps one whole row for each distinct value of the columns named, with every column SHOW lists, or the whole row after a GET. In the database this is SELECT DISTINCT ON. Rows that share a value are not equal: they differ in their other columns, so a choice of which one is kept has to be made.

Which row is kept. The first row of each value in the order the statement sorts. Whichever column SORT names first decides it:

  • In the database, the rows are ordered first by the DISTINCT columns, in the direction SORT gives each one, or ascending when SORT does not name it, and then by the rest of SORT. That rest decides which row of each value comes first and is kept: DISTINCT("contentType") with SORT("updatedAt DESC") keeps the latest entry of each kind, and the kinds come back in order.
  • After a SORT in a GET, DISTINCT runs over the already sorted rows and keeps the first of each value. SORT("updatedAt DESC") then DISTINCT e.contentType keeps the latest entry of each kind, with no rule about the order of the kinds.

Without a SORT, the statement does not say which row of a value is kept.

Empty (null) values count as one value: rows whose DISTINCT column is empty collapse to one row.

Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-2001A DISTINCT column does not exist on the tableCheck the column's name as the table gives it
HTQL-1003 Unsupported syntaxThe form does not fit the place: brackets after a GET, or the keyword form inside a LOOKUPUse DISTINCT("column") in a LOOKUP and DISTINCT alias.column after a GET
Placement and context

In a LOOKUP, DISTINCT is one of the call's arguments, written in any position. The database removes the repeats first, then orders the rows with SORT and cuts them with TAKE, so TAKE(5) keeps five different values and not five rows that may repeat.

After a GET and in a FOREACH body, DISTINCT is a step that runs where it is written, among FILTER, SORT, TAKE, LET, IF and RETURN:

Where it is writtenWhat runs
Before any SORT or TAKE and before any row step, and no earlier DISTINCTIn the database, with the first FILTER, as one query. The database removes the repeats, then sorts and cuts
After a SORT, a TAKE, a row step (a LET or IF that reads the row) or a RETURN, or as a second DISTINCTOver the rows at that point, in memory: it keeps the first row it meets for each value, in the order the rows then stand, and drops the rest

The first FILTER of a read belongs to the database when no TAKE is before it, and it removes rows before DISTINCT runs, wherever it stands among the leading steps. A second FILTER, a FILTER after a TAKE, and an IF run over the rows after DISTINCT.

Order matters. DISTINCT then TAKE(3) keeps three different values. TAKE(3) then DISTINCT keeps the different values among three rows.

Limitations
  • DISTINCT compares whole columns. It does not compare part of a value, a computed value or an alias of SHOW. Not supported: DISTINCT("LOWER(contentType)") and similar expressions.
  • In a LOOKUP, write the columns as quoted text inside brackets. After a GET, write them plainly with no brackets. Brackets after a GET, or a bare keyword in a LOOKUP, are not in HTQL's grammar.
  • A column that the table does not store is refused when DISTINCT runs in the database. After a SORT, TAKE or RETURN in a GET, DISTINCT reads the row's fields by name, and a name that no row has reads as empty for every row, so every row counts as the same value and one row is kept. Check the column's name.
  • DISTINCT does not count, sum or group. To count the rows of each value, use GROUP with COUNT in a LEARN.
  • DISTINCT does not sort. The rows of a LOOKUP come back in the database's order unless SORT is written, and after a GET the order is the order of the rows read.
  • In a FOREACH body, a DISTINCT that runs in the database before the loop changes which rows the loop visits.
Permissions and security

DISTINCT grants no access of its own. It reads the same table, with the same permission, as the statement it belongs to.

Best practices
  • To list the values of one column, SHOW that column in a LOOKUP with DISTINCT(), or write DISTINCT alias.column and RETURN that column in a GET.
  • For the latest row of each value, SORT by the latest first, then DISTINCT on the value.
  • Write the SORT that chooses the row, always: without it, the row kept is not stated.
  • Put DISTINCT before TAKE, so a page counts different values.