databases
10 minSQL Query Recipes: Joins, Grouping, Window Functions, and Safe Pagination
Build common analytics queries while avoiding duplicate rows, incorrect aggregates, unstable pages, and cross-dialect surprises.
Search the library
6 matching resultsArticles and interview answers
Q&A
Q&AWhy can’t a window-function rank usually be filtered in WHERE?
Window values are computed after WHERE in logical query processing.
Read the concise answer
WHERE filters source rows before window calculations are available. Put the window expression in a common table expression or derived table, then filter its rank in the outer query. This also makes the ranking input set explicit.
databases
10 minTop-N and Third-Highest Salary Queries in PostgreSQL, MySQL, and Oracle
Choose between distinct salary ranking, row ranking, and pagination syntax while handling ties and NULLs explicitly.
Q&A
Q&AHow do you find the third-highest distinct salary in SQL?
Use DENSE_RANK for distinct values and state whether you want salary values or employee rows.
Read the concise answer
Filter out NULL salaries, compute DENSE_RANK() OVER (ORDER BY salary DESC), then select rank 3. DENSE_RANK treats equal salaries as the same rank. PostgreSQL, MySQL 8+, and Oracle support this window-function approach; if you instead mean the third row, use ROW_NUMBER with a deterministic tie-breaker.
databases
10 minOracle SQL: Analytic Functions, Execution Plans, and Bind Variables
Use Oracle analytic SQL for ordered calculations, inspect plan operations, and understand why bind-variable behavior matters.
Q&A
Q&AHow are Oracle analytic functions different from GROUP BY?
Analytic functions calculate across a partition while keeping each input row in the result.
Read the concise answer
GROUP BY collapses rows into one row per group. An analytic function uses OVER with partition, ordering, and frame rules to calculate a value while preserving row detail. Specify a deterministic ordering and explicit frame for running totals when peer-row behavior would matter.