Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Method 1 (Subquery)**: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). **Method 2 (LIMIT/OFFSET)**: SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1—single value; ties pick one. **Method 3 (Window)**: SELECT...
Method 1 (Subquery): SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). Method 2 (LIMIT/OFFSET): SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1—single value; ties pick one. Method 3 (Window): SELECT salary FROM (SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) rn FROM employees) t WHERE rn = 2. Trade-offs: Subquery universal; LIMIT simple; ROW_NUMBER one row; DENSE_RANK for ties.
Red Flag: LIMIT/OFFSET with ties—arbitrary. Pro-Move: 'We compared: subquery 2 scans; LIMIT 1 sort; window 1 scan + rank. Window fastest on indexed salary for 1M rows.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.