How to Select the Top Row of Each Group in SQL?

Finding the most recent order per customer, the highest salary per department, etc., requires SQL Server to retrieve the top row of each group. ROW_NUMBER() and TOP WITH TIES are two of the several methods that SQL Server offers to accomplish this, and each is appropriate for a particular use case. In this blog, let’s explore the different approaches to getting each group’s top row in SQL Server.

Table of Contents:

• Methods to Get the Top Row of Each Group in SQL Server
  • Method 1: Using GROUP BY with Aggregate Function in SQL Server
  • Method 2: Using ROW_NUMBER Window Function in SQL Server
  • Method 3: Using TOP WITH TIES in SQL Server
  • Method 4: Using CROSS APPLY in SQL Server
  • Method 5: Using OUTER APPLY in SQL Server
  • Method 6: Using DISTINCT WITH FIRST_VALUE() in SQL Server
  • Method 7: Using DISTINCT WITH MAX() in SQL Server
  • Method 8: Using PARTITION BY WITH RANK() in SQL Server
• Performance Comparison of Each Method
• Real-world Examples
• Conclusion

Methods to Select the Top Row of Each Group in SQL

Depending on performance and use cases, SQL Server offers a variety of methods to effectively retrieve the top row per group, each with a unique advantage. ROW_NUMBER, TOP WITH TIES, and CROSS APPLY are the main strategies.

Before exploring the methods let us create an ORDERS table and insert some values into it. Let this ORDERS table be used as an example for the following methods.

--Create an ORDERS table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    OrderAmount DECIMAL(10,2)
);
--Insert some data into it
INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES 
(1001, 101, '2024-02-10', 500.00),
(1002, 101, '2024-02-15', 750.00),  
(1003, 102, '2024-02-08', 400.00),
(1004, 102, '2024-02-12', 650.00),  
(1005, 103, '2024-02-05', 300.00),
(1006, 103, '2024-02-18', 900.00);  

--To display the ORDERS table
Select * from Orders;

This is how the table looks like when it is created and inserted with the data.

Method 1: Using GROUP BY with Aggregate Function in SQL Server

We can group rows by a particular column and retrieve the maximum or most recent value from another column by combining GROUP BY with an aggregate function like MAX().

Syntax:

SELECT <group_column>, MAX(<sorting_column>) AS LatestValue
FROM <table_name>
GROUP BY <group_column>;

Example:

--To get the most recent order per customer
SELECT CustomerID, MAX(OrderDate) AS LatestOrderDate
FROM Orders
GROUP BY CustomerID;

Output:

Explanation: ‘GROUP BY CustomerID’ groups the records by CustomerID, and MAX (OrderDate) gets the most recent OrderDate for each Customer.

Method 2: Using ROW_NUMBER Window Function in SQL Server

Based on a specific sorting column, the ROW_NUMBER function gives each row in a group a distinct ranking.

Syntax:

WITH RankedData AS (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY <group_column> ORDER BY <sorting_column> DESC) AS rn
    FROM <table_name>
)
SELECT * FROM RankedData WHERE rn = 1;

Example:

--To get the most recent order per customer
WITH RankedOrders AS (
    SELECT OrderID, CustomerID, OrderDate, OrderAmount,
           ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn
    FROM Orders
)
SELECT OrderID, CustomerID, OrderDate, OrderAmount
FROM RankedOrders WHERE rn = 1;

Output:

Explanation: ROW_NUMBER() sorts the orders by OrderDate and assigns a unique ranking to each customer’s order. Only the most recent order for each customer is returned by the WHERE Clause.

Method 3: Using TOP WITH TIES in SQL Server

The TOP WITH TIES Clause guarantees that several records with the same ranking are included while permitting the retrieval of the first row per group.

Syntax:

SELECT TOP 1 WITH TIES <columns>
FROM <table_name>
ORDER BY ROW_NUMBER() OVER (PARTITION BY <group_column> ORDER BY <sorting_column> DESC);

Example:

--To get the lowest order amount per customer 
WITH RankedOrders AS (
    SELECT OrderID, CustomerID, OrderDate, OrderAmount,
           ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderAmount ASC) AS rn
    FROM Orders
)
SELECT TOP 1 WITH TIES OrderID, CustomerID, OrderDate, OrderAmount
FROM RankedOrders
ORDER BY rn;

Output:

Explanation: Here, each customer’s order is given a unique ranking by ROW_NUMBER(), which sorts them in ascending order by OrderDate. The WHERE Clause returns with the lowest amount per customer.

Method 4: Using CROSS APPLY in SQL Server

Each row from the main table can be joined with the top matching row from a subquery using the CROSS APPLY operator.

Syntax:

SELECT o.<columns>
FROM (SELECT DISTINCT <group_column> FROM <table_name>) c
CROSS APPLY (
    SELECT TOP 1 <columns>
    FROM <table_name> o
    WHERE o.<group_column> = c.<group_column>
    ORDER BY <sorting_column> DESC
) o;

Example:

SELECT o.OrderID, o.CustomerID, o.OrderDate, o.OrderAmount
FROM (SELECT DISTINCT CustomerID FROM Orders) c
CROSS APPLY (
    SELECT TOP 1 OrderID, OrderDate, OrderAmount, CustomerID
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) o;

Output:

Explanation: The most recent order for each customer is retrieved by the Cross App (top one order date DESC).

Method 5: Using OUTER APPLY in SQL Server

The OUTER APPLY operator returns every row from the left table, even if the right table contains no matching rows.

Syntax:

SELECT <columns>
FROM <main_table> m
OUTER APPLY (
    SELECT TOP 1 <columns>
    FROM <related_table> r
    WHERE r.<group_column> = m.<group_column>
    ORDER BY <sorting_column> DESC
) alias_name;

Example:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100)
);
INSERT INTO Customers (CustomerID, CustomerName) VALUES
(101, 'Alice'),
(102, 'Bob'),
(103, 'Charlie'),
(104, 'David');  -- No orders for this customer
--To get the most recent order per customer (including customers with no orders)
SELECT c.CustomerID, o.OrderID, o.OrderDate, o.OrderAmount
FROM Customers c
OUTER APPLY (
    SELECT TOP 1 OrderID, OrderDate, OrderAmount
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) o;

Output:

Explanation: The most recent order for each customer, arranged by OrderDate DESC, can be found using the OUTER APPLY. CustomerID = 104 with no orders is also included..

Method 6: Using DISTINCT WITH FIRST_VALUE() in SQL Server

By using FIRST_VALUE() inside a function, the DISTINCT WITH FIRST_VALUE enables you to choose the first row of each group.

Syntax:

SELECT DISTINCT column1, 
       FIRST_VALUE(column2) OVER (PARTITION BY column1 ORDER BY column3 DESC) AS column2,
       FIRST_VALUE(column4) OVER (PARTITION BY column1 ORDER BY column3 DESC) AS column4
FROM table_name;

Example:

--To retrieve the latest order per customer
SELECT DISTINCT CustomerID, 
       FIRST_VALUE(OrderID) OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS OrderID,
       FIRST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS OrderDate,
       FIRST_VALUE(OrderAmount) OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS OrderAmount
FROM Orders;

Output:

Explanation: Using OrderDate DESC, FIRST_VALUE() chooses each customer’s most recent order. Only one row per customer will show up in the final result thanks to DISTINCT.

Method 7: Using DISTINCT WITH MAX() in SQL Server

The most recent record for each group is extracted using the MAX() aggregate function in this DISTINCT WITH MAX(). The original table is then joined to retrieve the pertinent row.

Syntax:

SELECT DISTINCT t1.column1, t1.column2, t1.column3, ...
FROM table_name t1
JOIN (
    SELECT column_group, MAX(column_to_sort) AS latest_value
    FROM table_name
    GROUP BY column_group
) t2 ON t1.column_group = t2.column_group AND t1.column_to_sort = t2.latest_value;

Example:

--Retrieving the latest order per customer
SELECT DISTINCT o.CustomerID, o.OrderID, o.OrderDate, o.OrderAmount
FROM Orders o
JOIN (
    SELECT CustomerID, MAX(OrderDate) AS LatestOrderDate
    FROM Orders
    GROUP BY CustomerID
) latest ON o.CustomerID = latest.CustomerID AND o.OrderDate = latest.LatestOrderDate;

Output:

Explanation: The subquery finds the most recent OrderDate for each CustomerID using MAX(OrderDate). The main query links the subquery to the original Orders table in order to retrieve the entries that match the most recent order date.

Method 8: Using PARTITION BY WITH RANK() in SQL Server

This method finds the top row for each group using the RANK() window function. RANK(), as opposed to ROW_NUMBER(), assigns the same rank to rows with identical values and skips numbers for succeeding ranks.

Syntax:

SELECT column1, column2, column3, ...
FROM (
    SELECT column1, column2, column3, 
           RANK() OVER (PARTITION BY column_group ORDER BY column_to_sort DESC) AS rnk
    FROM table_name
) ranked
WHERE rnk = 1;

Example:

--To retrieve the latest order per customer using RANK()
SELECT OrderID, CustomerID, OrderDate, OrderAmount
FROM (
    SELECT OrderID, CustomerID, OrderDate, OrderAmount,
           RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rnk
    FROM Orders
) ranked
WHERE rnk = 1;

Output:

Explanation: The inner query uses RANK() to assign a rank to each CustomerID group. The outer query only filters rows where rnk = 1 to ensure that only the most recent order for each client is obtained.

Performance Comparison of Each Method

Real-world Examples

1. Customer Management System: To display each customer’s most recent purchase, an e-commerce system must fetch the most recent order.

Example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    OrderAmount DECIMAL(10,2)
);
--Insert sample data 
INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES
(1001, 101, '2024-02-10', 500.00),
(1002, 101, '2024-02-15', 750.00),
(1003, 102, '2024-02-08', 400.00),
(1004, 102, '2024-02-12', 650.00),
(1005, 103, '2024-02-05', 300.00),
(1006, 103, '2024-02-18', 900.00);
--Retrieve the latest record order per customer
SELECT OrderID, CustomerID, OrderDate, OrderAmount
FROM (
    SELECT OrderID, CustomerID, OrderDate, OrderAmount,
           ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn
    FROM Orders
) ranked
WHERE rn = 1;

Output:

Explanation: ROW_NUMBER() assigns a unique rank to every order per customer ordered by OrderDate. The WHERE Clause returns only the latest order per customer.

2. HR System: The HR Department wants to track and obtain the most recent salary of each employee from their salary history

Example:

CREATE TABLE EmployeeSalaries (
    SalaryID INT PRIMARY KEY,
    EmployeeID INT,
    SalaryAmount DECIMAL(10,2),
    EffectiveDate DATE
);
--Insert sample data
INSERT INTO EmployeeSalaries (SalaryID, EmployeeID, SalaryAmount, EffectiveDate) VALUES
(1, 201, 60000, '2023-01-01'),
(2, 201, 65000, '2024-01-01'),
(3, 202, 55000, '2023-02-01'),
(4, 202, 60000, '2024-02-01'),
(5, 203, 70000, '2023-03-01'),
(6, 203, 75000, '2024-03-01');
--To retrieve the most recent salary per employee
SELECT SalaryID, EmployeeID, SalaryAmount, EffectiveDate
FROM (
    SELECT SalaryID, EmployeeID, SalaryAmount, EffectiveDate,
           DENSE_RANK() OVER (PARTITION BY EmployeeID ORDER BY EffectiveDate DESC) AS rnk
    FROM EmployeeSalaries
) ranked
WHERE rnk = 1;

Output:

Explanation: The DENSE_RANK()  just ranks without skipping the number of ties, and the outer query filters rnk = 1, which fetches the latest salary per employee.

Conclusion

In SQL Server, retrieving the top row for each group is a very basic yet vital query for data management. Different options are available: ROW_NUMBER(), TOP WITH TIES, CROSS APPLY, etc. The decision on which to choose should be based on the specific performance requirements and data needs. Selecting the appropriate one is very important in keeping the accuracy and efficiency in working with grouped data. In this blog, you might have learned useful techniques for getting the top row per group in SQL Server.

To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts.

How to Select the Top Row of Each Group in SQL? – FAQs