solidDB Help : solidDB reference : SQL: Statements : SELECT
  
SELECT
SELECT [ALL | DISTINCT] select‑list [LEVEL]
   FROM table‑reference‑list
   [WHERE search‑condition]
   [GROUP BY column‑name [, column‑name …]]
   [HAVING search‑condition] [hierarchical‑condition]
   [[UNION | INTERSECT | EXCEPT] [ALL] select‑statement …]
   [ORDER BY expression] [ASC | DESC]]
   [row-limit-condition]
   [FOR UPDATE]
where:
select‑list::= see query‑specification and Pseudo columns in SELECT statement
table‑reference‑list::= see table‑reference
search‑condition::= see search‑condition
hierarchical‑condition::= START WITH search‑condition CONNECT BY [PRIOR] search‑condition
row-limit-condition::= LIMIT row‑count [OFFSET skipped‑rows] |
   LIMIT skipped‑rows, row‑count |
   FETCH FIRST ROW ONLY |
   FETCH FIRST constant ROWS ONLY
Access requirements
SELECT privileges on table
Usage
Use the SELECT statement to retrieve values from a database. You can select 0 or more records from one or more tables. A SELECT operation is typically called a ‘query’.
Parameters, clauses, keywords, and variables
LEVEL: A pseudocolumn that is valid in the context of the hierarchical query only. If the result set is viewed as a tree of inter‑referenced rows, the LEVEL column produces the tree level number, assigning "1" to the top-level row.
hierarchical‑condition: If your table contains hierarchical data, you can select rows in a hierarchical order.
START WITH: Specifies the root row(s) of the hierarchy.
CONNECT BY: Specifies the relationship between parent rows and child rows of the hierarchy. The CONNECT BY condition cannot contain a subquery.
PRIOR: A unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the expression that follows it for the parent row of the current row in a hierarchical query.
In a hierarchical query, one expression in the condition must be qualified with the PRIOR operator to refer to the parent row.
PRIOR is most commonly used when comparing column values with the equality operator. The PRIOR keyword can be on either side of the operator.
ORDER BY: Causes the rows at any level to be ordered accordingly. See also, expression.
row-limit-condition:
LIMIT: The non-standard clause LIMIT row‑count OFFSET skipped‑rows allows you to mask out a portion of a result set with a sliding window that has the size of row‑count and is positioned at row number (skipped‑rows + 1). A negative value of skipped‑rows results in an error, while the negative value of row‑count results in the whole result set.
Two formats are available: for example LIMIT 24 OFFSET 10 is equal to LIMIT 10, 24.
FETCH FIRST ROW ONLY: Returns only the first row of the table.
FETCH FIRST constant ROWS ONLY: Returns the number of rows defined by constant.
FOR UPDATE: Row is locked with an update mode lock. This means that no other user can read or update the row, and ensures the current user can update the row later, see Concurrency control and locking.
Examples
SELECT ID FROM TEST;
SELECT DISTINCT ID, C FROM TEST WHERE ID = 5;
SELECT DISTINCT ID FROM TEST ORDER BY ID ASC;
SELECT NAME, ADDRESS FROM CUSTOMERS
UNION
SELECT NAME, DEP FROM PERSONNEL;
SELECT dept, count(*) FROM person
GROUP BY dept
ORDER BY dept
LIMIT 20 OFFSET 10
Example of LIMIT and FETCH
SELECT * FROM SYS_TABLES LIMIT 1;
SELECT * FROM SYS_TABLES FETCH FIRST ROW ONLY; -- same as above
SELECT * FROM SYS_TABLES WHERE TABLE_NAME LIKE 'A%' LIMIT 5; SELECT * FROM SYS_TABLES WHERE TABLE_NAME LIKE 'A%' FETCH FIRST 5 ROWS ONLY; -- same as above
Example of START WITH
SELECT last_name, employee_id, manager_id, LEVEL FROM employees START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name;
LAST_NAME  EMPLOYEE_ID  MANAGER_ID
=========  ===========  ==========
King       100
Cambrault  148          100
Bates      172          148
Bloom      169          148
Fox        170          148
Kumar      173          148
Ozer       168          148
Smith      171          148
De Haan    102          100
Hunold     103          102
Austin     105          103
Ernst      104          103
Lorentz    107          103
Pataballa  106          103
Errazuriz  147          100
Ande       166          147
Banda      167          147
Example of LEVEL and ORDER SIBLINGS BY
SELECT last_name, employee_id, manager_id, LEVEL FROM employees
START WITH last_name = 'King' CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name ORDER BY LEVEL;
LAST_NAME  EMPLOYEE_ID  MANAGER_ID  LEVEL
=========  ===========  ==========  =====
King       100          NULL        1
Cambrault  148          100         2
De Haan    102          100         2
Bates      172          148         3
Bloom      169          148         3
Gates      104          148         3
Hunold     103          102         3
Hope       202          172         4
Smith      201          172         4
Go up to
SQL: Statements