SQL Guide : solidDB® 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]] [LIMIT row_count [OFFSET skipped_rows] | LIMIT skipped_rows,row_count |
FETCH FIRST ROW ONLY | FETCH FIRST constant ROWS ONLY] [FOR UPDATE]
Usage
The SELECT statement retrieves values from a database. You can select 0 or more records from one or more tables. A SELECT operation is typically called a query.
LEVEL
LEVEL is a pseudocolumn valid in the context of the hierarchical query only. If the result set is viewed as a tree of interreferenced rows, the LEVEL column produces the tree level number, assigning "1" to the top-level row.
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
If your table contains hierarchical data, you can select rows in a hierarchical order using a hierarchical query clause. In a hierarchical query clause, START WITH specifies the root row(s) of the hierarchy and CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. The CONNECT BY condition cannot contain a subquery.
In a hierarchical query, one expression in the condition must be qualified with the PRIOR operator to refer to the parent row. PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query. 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 expression
ORDER SIBLINGS BY causes the rows at any level to be ordered accordingly. See also expression.
LIMIT
The non-standard clause LIMIT row_count OFFSET skipped_rows allows to mask out a portion of a result set with a sliding window having the size of row_count and positioned at the skipped_rows+1 row. A negative value of skipped_rows results in an error, while the negative value of row_count results in the whole result set produced.
Two formats are available: for example LIMIT 24 OFFSET 10 is equal to LIMIT 10, 24.
FETCH
The FETCH FIRST ROW ONLY clause returns only the first row of the table. FETCH FIRST constant ROWS ONLY returns the number of rows defined with constant.
FOR UPDATE
When a user accesses a row with the SELECT... FOR UPDATE statement, the 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 later update the row. 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
See also
solidDB® SQL statements