In SQL Server, it's common to encounter scenarios where you need to extract the top record from each group within a dataset. One effective approach for achieving this involves utilizing the ROW_NUMBER() function alongside a common table expression (CTE). Below, we'll outline a step-by-step process to accomplish this task.

Let's consider a practical example.

Firstly we will create a sales data table, 'SalesData', which contains two key columns: 'Region' and 'SalesAmount'.

Syntax for creating 'SalesData' table:

CREATE TABLE SalesData (
    Region VARCHAR(50),
    SalesAmount DECIMAL(10, 2)
);

-- Inserting sample data
INSERT INTO SalesData (Region, SalesAmount) VALUES ('North', 15000);
INSERT INTO SalesData (Region, SalesAmount) VALUES ('North', 18000);
INSERT INTO SalesData (Region, SalesAmount) VALUES ('South', 20000);
INSERT INTO SalesData (Region, SalesAmount) VALUES ('South', 25000);
INSERT INTO SalesData (Region, SalesAmount) VALUES ('East', 22000);
INSERT INTO SalesData (Region, SalesAmount) VALUES ('East', 24000);



Query to get top 1 sales record for each region

WITH CTE AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Region ORDER BY SalesAmount DESC) AS RowNum
    FROM SalesData
)

SELECT Region, SalesAmount
FROM CTE
WHERE RowNum = 1;

Result:


Let's Breakdown this Syntax:


WITH CTE AS: 
This part starts a Common Table Expression (CTE) named 'CTE'. A CTE is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.


SELECT *, ROW_NUMBER() OVER (PARTITION BY Region ORDER BY SalesAmount DESC) AS RowNum: 
This part of the code selects all columns (denoted by '*') from the table 'SalesData' and computes the row number for each row within the partitions defined by the 'Region' column. The ROW_NUMBER() function assigns a unique integer value to each row, ordered by the 'SalesAmount' column in descending order within each partition. The result of this computation is aliased as 'RowNum'.


FROM SalesData: This clause specifies the source table from which the data is being selected.


SELECT Region, SalesAmount FROM CTE WHERE RowNum = 1:
This part of the code selects the 'Region' and 'SalesAmount' columns from the CTE 'CTE' where the value of 'RowNum' is 1. In other words, it retrieves the rows from the CTE where the row number is 1, which corresponds to the top record for each partition (in this case, for each distinct 'Region') based on the 'SalesAmount' in descending order.


By comprehensively understanding and utilizing these SQL functionalities, you can effectively manage and analyze data, facilitating informed decision-making processes within your database operations.


Learn More: