How do you sum values for each group without GROUP BY and still keep every row? Here is how SUM with OVER and PARTITION BY answers a reader’s question.
How Does TOP WITH TIES Work in SQL Server? – Interview Question of the Week #159
Question: How Does TOP WITH TIES Work in SQL Server?
Answer: This is an ancient question I just encountered in the recent Comprehensive Database Performance Health Check.
How to Keep Certain Records on Top While Ordering Data? – Interview Question of the Week #110
An interview question: how do you keep one city on top while ordering data by name for the rest? The answer is a small trick in the ORDER BY clause.
Sort Query Using Dynamic Variables Without EXEC – Interview Question of the Week #074
How do you sort query results by a column passed in a variable? Here is how to sort query output dynamically without EXEC or sp_executesql.
LAG and LEAD: Comparing Each Row With the One Before It
Use LAG and LEAD for monthly comparisons, handle defaults and percent change, choose explicit frames, and detect status transitions.
SQL SERVER – MySQL – Order Column by Nearest Value for Any Integer
How do you order column values by how close they are to any integer, such as -5? Here is a script that works the same way on SQL Server and MySQL.
Why a Clustered Index Does Not Guarantee Order Without ORDER BY
Explain order without ORDER BY through parallel scans, NOLOCK allocation scans, alternate indexes, shared scans, and the explicit sort fix.