SELECT is the foundation of all queries in High-performance Search Edition. Every advanced query clause — filtering, grouping, sorting, and pagination — builds on a SELECT statement. For advanced query syntax, see the related topics.
Syntax
SELECT [ DISTINCT ]
{ * | projectItem [, projectItem ]* }
FROM tableExpression
[ WHERE booleanExpression ]
[ GROUP BY { groupItem [, groupItem ]* } ]
[ ORDER BY { orderByItem [, orderByItem ]* } ]
[ HAVING booleanExpression ]
[ LIMIT number ]
[ OFFSET number ]
projectItem:
expression [ [ AS ] columnAlias ]
| tableAlias . *Clauses
SELECT
Returns all columns or a specific set of columns from the source table.
Example 1: Return all columns.
SELECT * FROM table;Example 2: Return specific columns and assign an alias.
SELECT f1, f2 AS ff FROM table;Example 3: Return distinct values, removing duplicates.
SELECT DISTINCT f FROM table;WHERE
Filters rows before any grouping occurs. Only rows where booleanExpression evaluates to true are passed to subsequent clauses.
GROUP BY
Groups rows that share the same values in the specified columns, typically used with aggregate functions.
SELECT f1, count(*) AS num
FROM t1
GROUP BY f1;ORDER BY
Sorts the result set by one or more columns.
HAVING
Filters groups after GROUP BY aggregation. Use HAVING for conditions on aggregate results.
Difference from WHERE:
| Clause | Operates on | Evaluated |
|---|---|---|
WHERE | Individual rows | Before grouping |
HAVING | Aggregated groups | After grouping |
LIMIT
Restricts the number of rows returned.
OFFSET
Skips a specified number of rows before returning results. Use OFFSET with LIMIT for paginated queries.
The following example counts rows per group in a subquery, then filters to groups where num exceeds 100:
SELECT * FROM (
SELECT f1, count(*) AS num
FROM t1
GROUP BY f1
) tt
WHERE tt.num > 100;