# Commands

### Commons (Data Manipulation)

#### SELECT

{% hint style="info" %}

```
SELECT [ DISTINCT [ ON ( expression [,...] ) ] | ALL ]
selectExpression [,...]
[ FROM tableExpression [,...] ]
[ WHERE expression ]
[ GROUP BY groupingElement [,...] ] [ HAVING expression ]
[ WINDOW { { windowName AS windowSpecification } [,...] } ]
[ QUALIFY expression ]
[ { UNION [ ALL ] | EXCEPT | INTERSECT } query ]
[ ORDER BY selectOrder [,...] ]
[ OFFSET expression { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ expression [ PERCENT ] ] { ROW | ROWS }
{ ONLY | WITH TIES } ]
[ FOR UPDATE ]
```

{% endhint %}

Selects data from a table or multiple tables.

Command is executed in the following logical order:

1. Data is taken from a table value expressions that are specified in the **`FROM`** clause, joins are executed. If **`FROM`** clause is not specified a single row is constructed.
2. **`WHERE`** filters rows. Aggregate or window functions are not allowed in this clause.
3. **`GROUP BY`** groups the result by the given expression(s). if **`GROUP BY`** clause is not specified, but non-window aggregate function are used or **`HAVING`** is specified all rows are grouped together.
4. Aggregate functions are evaluated.
5. Having filters rows after grouping and evaluation of aggregate functions. Non-window aggregate functions are allowed in this clause.
6. Window functions are evaluated.
7. **`QUALIFY`** filters rows after evaluation of window functions. Aggregate and window functions are allowed in this clause. (Non-standard syntax)
8. **`DISTINCT`** removes duplicates. If **`DISTINCT`** is used only the specified expressions are checked for duplicated; **`ORDER BY`** caluse, if any, is used to datetime preserved rows. First row is each **`DISTINCT ON`** group is preserved. In absence of **`ORDER BY`** preserved rows are not datermined, database may choose any row from each **`DISTINCT ON`** group.
9. **`UNION`**, **`EXCEPT`** and **`INTERSECT`** combine the result of this query with the results of another query. **`INTERSECT`** has higher precedence than **`UNION`** and **`EXCEPT`**. Operators with equal precedence are evaluated from left to right.
10. **`ORDER BY`** sorts the result by the given column(s) or expression(s).
11. Number of rows in output can be limited with **`OFFSET`** and **`FETCH`** clauses. **`OFFSET`** specifies how many rows to skip. Please note that queries with high offset values can be slow. **`FETCH FIRST/NEXT`** limits the number of rows returned by the query. If **`PERCENT`** is specified number of rows is specified as a percent of the total number of rows and should be an integer value between 0 and 100 inclusive. **`WITH TIES`** can be used only together with **`ORDER BY`** and means that all additional rows that have the same sorting position as the last row will be also returned.

Example:

```
SELECT * FROM TEST;
SELECT * FROM TEST ORDER BY NAME;
SELECT ID, COUNT(*) FROM TEST GROUP BY ID;
SELECT NAME, COUNT(*) FROM TEST GROUP BY NAME HAVING COUNT(*) > 2;
SELECT 'ID' COL, MAX(ID) AS MAX FROM TEST UNION SELECT 'NAME', MAX(NAME) FROM TEST;
SELECT * FROM TEST OFFSET 1000 ROWS FETCH FIRST 1000 ROWS ONLY;
SELECT A, B FROM TEST ORDER BY A FETCH FIRST 10 ROWS WITH TIES;
SELECT * FROM (SELECT ID, COUNT(*) FROM TEST
    GROUP BY ID UNION SELECT NULL, COUNT(*) FROM TEST)
    ORDER BY 1 NULLS LAST;
SELECT DISTINCT C1, C2 FROM TEST;
SELECT DISTINCT ON(C1) C1, C2 FROM TEST ORDER BY C1;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://ciusji.gitbook.io/guinsoo/reference/commands.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.