ORDER BY, LIMIT and DISTINCT

SELECT * FROM employees
ORDER BY salary DESC;

DESC sorts highest to lowest. ASC (default) sorts lowest to highest. You can sort by multiple columns: ORDER BY department ASC, salary DESC.

SELECT * FROM employees
ORDER BY salary DESC
LIMIT 5;

LIMIT restricts the number of rows returned. In SQL Server use TOP 5 instead. In Oracle use ROWNUM or FETCH FIRST 5 ROWS ONLY.

SELECT DISTINCT department
FROM employees;

DISTINCT removes duplicate values. If you SELECT DISTINCT on multiple columns, it returns unique combinations of all selected columns.

SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Classic interview question. Alternative using LIMIT: SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

-- For Nth highest (e.g. 3rd):
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET N-1;

-- Example for 3rd highest:
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

OFFSET skips rows. OFFSET 0 = 1st row, OFFSET 1 = 2nd row, OFFSET N-1 = Nth row. This is a very common interview question at MNCs in Delhi NCR.

SELECT COUNT(DISTINCT department)
FROM employees;

COUNT(DISTINCT column) counts unique non-NULL values. Different from COUNT(*) which counts all rows.