Sorting and Limiting

## Learning Objectives - Master ORDER BY - Sort by multiple columns - Use ASC and DESC - Limit results effectively ## ORDER BY Clause ### Basic Sorting ```sql SELECT first_name, last_name, salary FROM employees ORDER BY last_name; ``` ### Ascending (Default) ```sql SELECT * FROM employees ORDER BY salary ASC; -- ASC is default, so same as: ORDER BY salary; ``` ### Descending ```sql SELECT * FROM employees ORDER BY salary DESC; ``` ## Sort by Multiple Columns ### Sequential Sorting ```sql SELECT department, first_name, last_name, salary FROM employees ORDER BY department ASC, salary DESC; ``` ### How It Works 1. First, sort by department (A-Z) 2. Within each department, sort by salary (highest first) ### Example Result ```text department | first_name | salary -----------+------------+-------- Engineering| Carol | 95000 Engineering| Alice | 85000 Sales | Bob | 75000 Sales | Dave | 70000 ``` ## Sort by Column Position ### Using Numbers ```sql SELECT first_name, last_name, salary FROM employees ORDER BY 3 DESC; -- Sort by 3rd column (salary) ``` ### Not Recommended Position numbers are harder to read and maintain. ## NULL in Sorting ### Placement (Database Dependent) ```sql -- Most databases: NULLs first with ASC, last with DESC ORDER BY hire_date ASC; -- NULLs first ORDER BY hire_date DESC; -- NULLs last -- Explicit control ORDER BY hire_date ASC NULLS FIRST; ORDER BY hire_date DESC NULLS LAST; ``` ## ORDER BY with Expressions ### Calculate and Sort ```sql SELECT first_name, salary, salary * 12 AS annual_salary FROM employees ORDER BY annual_salary DESC; ``` ### Sort by Length ```sql -- Longest names first SELECT first_name FROM employees ORDER BY LENGTH(first_name) DESC; ``` ## LIMIT / FETCH ### MySQL / PostgreSQL / SQLite ```sql -- Top 5 highest paid SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC LIMIT 5; ``` ### SQL Server ```sql SELECT TOP 5 first_name, last_name, salary FROM employees ORDER BY salary DESC; ``` ### PostgreSQL FETCH ```sql SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY; ``` ### ANSI SQL FETCH ```sql SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS WITH TIES; ``` ## OFFSET ### Skip Rows ```sql -- Skip first 10, get next 5 SELECT * FROM employees ORDER BY salary DESC LIMIT 5 OFFSET 10; ``` ### Pagination Query ```sql -- Page 1 SELECT * FROM employees ORDER BY employee_id LIMIT 10 OFFSET 0; -- Page 2 SELECT * FROM employees ORDER BY employee_id LIMIT 10 OFFSET 10; -- Page 3 SELECT * FROM employees ORDER BY employee_id LIMIT 10 OFFSET 20; ``` ## DISTINCT with ORDER BY ```sql -- Get unique departments, alphabetically SELECT DISTINCT department FROM employees ORDER BY department; ``` ## Common Patterns ### Top N per Group ```sql -- Highest paid in each department SELECT * FROM employees e1 WHERE salary = ( SELECT MAX(salary) FROM employees e2 WHERE e2.department = e1.department ) ORDER BY department, salary DESC; ``` ### First/Last Record ```sql -- Most recent hire SELECT * FROM employees ORDER BY hire_date DESC LIMIT 1; -- First hired SELECT * FROM employees ORDER BY hire_date ASC LIMIT 1; ``` ## Performance Considerations ### Index Usage ```sql -- Fast with index on salary ORDER BY salary DESC; -- Slow without index ORDER BY salary + (bonus * 12) DESC; ``` ### LIMIT Without ORDER BY ```sql -- Unpredictable results LIMIT 10; -- Which 10? -- Always use ORDER BY with LIMIT ORDER BY employee_id LIMIT 10; ``` ## Summary - `ORDER BY` sorts results - `ASC` ascending (default), `DESC` descending - Sort multiple columns: first by first, then by second - `NULL` placement varies; use `NULLS FIRST/LAST` if supported - `LIMIT` / `TOP` restricts row count - `OFFSET` skips rows for pagination - Always use `ORDER BY` with `LIMIT` for predictable results