Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting
Retrieving Data with SELECT
This domain tests single-table retrieval: choosing columns, filtering rows, sorting, removing duplicates, and limiting rows. Oracle evaluates FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, then row limiting.
Core rules
- The SELECT list can include columns, expressions, aliases, and concatenation with ||. Use q'[...]' for string literals containing quotes.
- WHERE filters rows before grouping. It cannot reference a SELECT-list alias; use the expression or an inline view.
- ORDER BY sorts by column, alias, expression, or position. Control NULL placement with NULLS FIRST or NULLS LAST; Oracle defaults NULLS LAST for ASC and NULLS FIRST for DESC.
- DISTINCT applies to the whole select list, not one column.
- Oracle 12c row limiting: OFFSET n ROWS FETCH NEXT m ROWS ONLY, or FETCH FIRST m ROWS ONLY. WITH TIES requires ORDER BY.
- ROWNUM is assigned before ORDER BY, so WHERE ROWNUM <= 3 does not reliably give top three salaries.
Worked example
SELECT employee_id, salary FROM employees WHERE department_id = 50 ORDER BY salary DESC FETCH FIRST 3 ROWS ONLY; The WHERE clause keeps department 50 rows, ORDER BY sorts them highest salary first, and FETCH returns exactly three rows. Without ORDER BY, the three rows are nondeterministic.
Traps
- Using a column alias in WHERE.
- Expecting ROWNUM to work after sorting.
- Using DISTINCT and ordering by a column not in the select list.
- Assuming FETCH FIRST returns ties automatically; use WITH TIES.