A few years ago, an HR analyst at a logistics company asked me why her “top 3 earners” report showed three people one week and five the next. Nothing in the data had changed. Two employees shared the same salary, and the query she copied from a forum quietly cut off one of them. Leadership … Read more
ROW_NUMBER() is a common function in SQL Server. It generates a number in sequential order for each row, beginning with 1. To make sure that the numbers are assigned in the correct order, we must always use the ORDER BY procedure. Data types of BIG INT are used for the values that this function returns. … Read more
Understanding row_number vs rank vs dense_rank in SQL is one of the highest-value skills for anyone writing T-SQL reports. These three window functions look nearly identical, yet they produce different results the moment two rows share a value. Picking the wrong one causes duplicate invoice numbers, missing rows, and reports that change between runs. This … Read more
In this comprehensive tutorial, I will guide you through a deep-dive exploration of SQL RANK and DENSE_RANK. We will break down their mathematical differences, analyze their evaluation mechanics inside the query optimizer, dissect the role of the PARTITION BY clause, and establish foundational best practices. SQL RANK vs DENSE_RANK The Genesis of Analytic Rank: Understanding … Read more
A payroll dashboard skips second place after two employees share the highest salary. Another report forces those same employees into different positions. Both queries run without errors, but neither matches the business requirement: equal salaries should share a rank, with no gaps afterward. The DENSE_RANK SQL function solves that problem. It assigns consecutive ranks to … Read more
A payroll analyst at a mid-sized staffing company once sent me a spreadsheet and a worried email. Their HR dashboard showed the “second highest salary” per department, but two departments returned the same number as the top salary. The query had used TOP 1 with a sort, and it counted tied employees as separate ranks. … Read more
A payroll report lists the lowest-paid employee twice because two people earn the same amount. A developer skips the first row to find the second-lowest salary, but the query returns that same minimum again. The statement runs successfully; the business answer is wrong. Understanding how to find the second lowest salary in SQL Server starts … Read more
A payroll report can return the wrong employee even when its SQL runs without errors. Two employees share the highest salary, and a query that simply selects the third row reports the second-highest salary instead of the third distinct amount. Understanding how to find 3rd highest salary in SQL Server starts with defining what “third … Read more
A payroll analyst at a mid-size staffing company once sent me a screenshot of a report. It showed the “third highest salary” in a department, and the number was wrong. Two people shared the top salary, and the query counted them as separate ranks. The report fed a compensation review, so a small logic bug … Read more
When I trained a new database developer on my team last year, one of the first questions he asked me was, “How do I actually save this stored procedure once I’ve written it?” It’s a fair question, because unlike saving a Word document or an Excel file, saving a stored procedure in SQL Server doesn’t … Read more