How to Select the Latest Record in SQL Server (Per Group Too)

Sales wants two numbers on the Monday dashboard: the newest order in the system, and each customer’s most recent order. One tie in the data decides which query you can trust.

To select the latest record in SQL Server, sort by the date column descending and keep one row: SELECT TOP (1) * FROM dbo.Orders ORDER BY OrderDate DESC, OrderID DESC; The second sort column breaks ties.

In this tutorial, I’ll answer the “how do I select latest record in SQL Server” question for a whole table and for each customer (or user), one query at a time, on SQL Server 2025.

Here’s what we’re building, each customer’s latest order with the tie settled on every run:

WITH Ranked AS (
    SELECT OrderID, Customer, OrderDate, Total,
           ROW_NUMBER() OVER (
               PARTITION BY CustomerID
               ORDER BY OrderDate DESC, OrderID DESC) AS rn
    FROM dbo.Orders
)
SELECT OrderID, Customer, OrderDate, Total
FROM Ranked
WHERE rn = 1
ORDER BY OrderDate DESC;

Output:

OrderID     Customer         OrderDate           Total       
----------- ---------------- ------------------- ------------
       1010 Megan Bowen      2026-10-21 15:45:00       120.00
       1008 Alex Wilber      2026-10-19 09:20:00        54.30
       1007 Adele Vance      2026-10-14 13:10:00        67.20
       1005 Diego Siciliani  2026-10-02 08:05:00       432.75

(4 rows affected)

Before You Start: The Sample Orders Table

ROW_NUMBER() and APPLY need SQL Server 2005+. I ran every script with sqlcmd in VS Code, in a demo database called DfqLatestRecord:

CREATE TABLE dbo.Customers (
    CustomerID int          PRIMARY KEY,
    Customer   nvarchar(16) NOT NULL,
    State      char(2)      NOT NULL
);
CREATE TABLE dbo.Orders (
    OrderID    int           PRIMARY KEY,
    CustomerID int           NOT NULL REFERENCES dbo.Customers,
    Customer   nvarchar(16)  NOT NULL,
    OrderDate  smalldatetime NULL,        -- NULL = draft, not placed yet
    Total      decimal(10,2) NOT NULL
);

INSERT INTO dbo.Customers (CustomerID, Customer, State) VALUES
(1, N'Megan Bowen',     'WA'),
(2, N'Alex Wilber',     'TX'),
(3, N'Adele Vance',     'CA'),
(4, N'Diego Siciliani', 'NY'),
(5, N'Lee Gu',          'IL');

INSERT INTO dbo.Orders (OrderID, CustomerID, Customer, OrderDate, Total) VALUES
(1001, 1, N'Megan Bowen',     '2026-09-03 09:15',  245.50),
(1002, 2, N'Alex Wilber',     '2026-09-08 14:02',   89.99),
(1003, 3, N'Adele Vance',     '2026-09-15 11:40', 1210.00),
(1004, 1, N'Megan Bowen',     '2026-09-29 16:25',  312.15),
(1005, 4, N'Diego Siciliani', '2026-10-02 08:05',  432.75),
(1006, 2, N'Alex Wilber',     '2026-10-06 10:30',  158.00),
(1007, 3, N'Adele Vance',     '2026-10-14 13:10',   67.20),
(1008, 2, N'Alex Wilber',     '2026-10-19 09:20',   54.30),
(1009, 1, N'Megan Bowen',     '2026-10-21 15:45',  990.40),
(1010, 1, N'Megan Bowen',     '2026-10-21 15:45',  120.00);  -- same timestamp as 1009

Watch orders 1009 and 1010: both Megan Bowen’s, both at 3:45 PM on 10/21/2026. I used smalldatetime to keep sqlcmd output narrow (SQL Server datetime vs datetime2).

Step 1: Get the Latest Record With TOP (1) and ORDER BY DESC

Sort by OrderDate newest first and keep the top row, then run the same query WITH TIES:

SET NOCOUNT ON;
-- The latest order in the table
SELECT TOP (1) OrderID, Customer, OrderDate, Total
FROM dbo.Orders
ORDER BY OrderDate DESC;

-- Every order that shares that latest timestamp
SELECT TOP (1) WITH TIES OrderID, Customer, OrderDate, Total
FROM dbo.Orders
ORDER BY OrderDate DESC;

Output:

OrderID     Customer         OrderDate           Total       
----------- ---------------- ------------------- ------------
       1009 Megan Bowen      2026-10-21 15:45:00       990.40
OrderID     Customer         OrderDate           Total       
----------- ---------------- ------------------- ------------
       1009 Megan Bowen      2026-10-21 15:45:00       990.40
       1010 Megan Bowen      2026-10-21 15:45:00       120.00

Checkpoint: TOP (1) returns 1009; WITH TIES returns 1009 and 1010.

Important: SQL Server may return either tied row. No date column? Sort by a key; you can’t select the last records without sorting.

Step 2: Add a Tie-Breaker So the Latest Record Never Changes

Add a unique column, like the primary key, as the last sort key:

-- Newest date first, then the highest OrderID
SELECT TOP (1) OrderID, Customer, OrderDate, Total
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderID DESC;

Output:

OrderID     Customer         OrderDate           Total       
----------- ---------------- ------------------- ------------
       1010 Megan Bowen      2026-10-21 15:45:00       120.00

(1 rows affected)

Checkpoint: order 1010 ($120.00) wins on every run, because it has the higher OrderID. A WHERE OrderDate = (SELECT MAX(OrderDate) ...) subquery has no such fix; it returns both tied rows.

Step 3: Get the Latest Record for Each Customer With MAX() and GROUP BY

For the latest record for each user or customer, most of us first try MAX() with GROUP BY, then join back:

-- MAX() + GROUP BY finds each latest date; join back for the row
SELECT o.OrderID, o.Customer, o.OrderDate, o.Total
FROM dbo.Orders AS o
JOIN (SELECT CustomerID, MAX(OrderDate) AS LastOrder
      FROM dbo.Orders
      GROUP BY CustomerID) AS m
  ON m.CustomerID = o.CustomerID
 AND m.LastOrder  = o.OrderDate
ORDER BY o.OrderDate DESC;

Output:

OrderID     Customer         OrderDate           Total       
----------- ---------------- ------------------- ------------
       1009 Megan Bowen      2026-10-21 15:45:00       990.40
       1010 Megan Bowen      2026-10-21 15:45:00       120.00
       1008 Alex Wilber      2026-10-19 09:20:00        54.30
       1007 Adele Vance      2026-10-14 13:10:00        67.20
       1005 Diego Siciliani  2026-10-02 08:05:00       432.75

(5 rows affected)

Checkpoint: five rows for four customers, because 1009 and 1010 both match Megan’s MAX date. For a login log, swap in UserID and LoginTime (SQL Server join with max date).

Step 4: Number Each Customer’s Orders With ROW_NUMBER() OVER (PARTITION BY)

ROW_NUMBER() restarts at 1 for each CustomerID and counts newest first. RANK() sits next to it so you can see the tie:

SET NOCOUNT ON;
SELECT OrderID, Customer, OrderDate,
       ROW_NUMBER() OVER (PARTITION BY CustomerID
                          ORDER BY OrderDate DESC) AS rn,
       RANK()       OVER (PARTITION BY CustomerID
                          ORDER BY OrderDate DESC) AS rnk
FROM dbo.Orders
WHERE CustomerID IN (1, 2)   -- Megan and Alex
ORDER BY Customer, OrderDate DESC;

Output:

OrderID     Customer         OrderDate           rn                   rnk                 
----------- ---------------- ------------------- -------------------- --------------------
       1008 Alex Wilber      2026-10-19 09:20:00                    1                    1
       1006 Alex Wilber      2026-10-06 10:30:00                    2                    2
       1002 Alex Wilber      2026-09-08 14:02:00                    3                    3
       1009 Megan Bowen      2026-10-21 15:45:00                    1                    1
       1010 Megan Bowen      2026-10-21 15:45:00                    2                    1
       1004 Megan Bowen      2026-09-29 16:25:00                    3                    3
       1001 Megan Bowen      2026-09-03 09:15:00                    4                    4

Checkpoint: Megan’s tie gets rn 1 and 2 (arbitrary) but rnk 1 and 1.

Wrap it in a CTE, add OrderID DESC to the window and filter rn = 1: that’s the finished query. Filter rnk = 1 to keep ties (SQL Server ROW_NUMBER tutorial).

Step 5: Get the Latest Order per Customer With CROSS APPLY (SELECT TOP 1)

CROSS APPLY runs a TOP (1) query once for each customer:

SET NOCOUNT ON;
SELECT c.Customer, x.OrderID, x.OrderDate, x.Total
FROM dbo.Customers AS c
CROSS APPLY (SELECT TOP (1) o.OrderID, o.OrderDate, o.Total
             FROM dbo.Orders AS o
             WHERE o.CustomerID = c.CustomerID
             ORDER BY o.OrderDate DESC, o.OrderID DESC) AS x;

-- OUTER APPLY keeps Lee Gu, who has no orders
SELECT c.Customer, x.OrderID
FROM dbo.Customers AS c
OUTER APPLY (SELECT TOP (1) o.OrderID FROM dbo.Orders AS o
             WHERE o.CustomerID = c.CustomerID
             ORDER BY o.OrderDate DESC, o.OrderID DESC) AS x
WHERE c.CustomerID = 5;

Output:

Customer         OrderID     OrderDate           Total       
---------------- ----------- ------------------- ------------
Megan Bowen             1010 2026-10-21 15:45:00       120.00
Alex Wilber             1008 2026-10-19 09:20:00        54.30
Adele Vance             1007 2026-10-14 13:10:00        67.20
Diego Siciliani         1005 2026-10-02 08:05:00       432.75
Customer         OrderID    
---------------- -----------
Lee Gu                  NULL

Checkpoint: one latest order for each of four customers. OUTER APPLY keeps Lee Gu with a NULL, like a SQL Server LEFT JOIN.

Step 6: Get the Latest N Records per Customer

Change Step 2’s TOP (1) to TOP (1000) for the “last 1000 rows”. Per customer, filter rn <= 2:

WITH Ranked AS (
    SELECT OrderID, Customer, OrderDate,
           ROW_NUMBER() OVER (PARTITION BY CustomerID
               ORDER BY OrderDate DESC, OrderID DESC) AS rn
    FROM dbo.Orders
)
SELECT OrderID, Customer, OrderDate, rn
FROM Ranked
WHERE rn <= 2
ORDER BY Customer, rn;

Output:

OrderID     Customer         OrderDate           rn                  
----------- ---------------- ------------------- --------------------
       1007 Adele Vance      2026-10-14 13:10:00                    1
       1003 Adele Vance      2026-09-15 11:40:00                    2
       1008 Alex Wilber      2026-10-19 09:20:00                    1
       1006 Alex Wilber      2026-10-06 10:30:00                    2
       1005 Diego Siciliani  2026-10-02 08:05:00                    1
       1010 Megan Bowen      2026-10-21 15:45:00                    1
       1009 Megan Bowen      2026-10-21 15:45:00                    2

(7 rows affected)

Note: with ties, RANK() <= 2 keeps two of Megan’s orders but DENSE_RANK() <= 2 keeps three (the PostgreSQL RANK function works the same).

Step 7: Add an Index on (CustomerID, OrderDate DESC) for Latest-Record Queries

On big tables, every pattern above wants rows sorted by customer, newest first:

CREATE INDEX IX_Orders_Customer_OrderDate
    ON dbo.Orders (CustomerID, OrderDate DESC, OrderID DESC)
    INCLUDE (Customer, Total);

The key matches the PARTITION BY, ORDER BY and tie-breaker, so SQL Server reads each customer’s newest row without a sort.

Test the Latest-Record Query With a New Order and a NULL Date

I added a new order for Alex and an undated draft for Adele, then re-ran the finished query:

SET NOCOUNT ON;
INSERT INTO dbo.Orders VALUES
 (1011, 2, N'Alex Wilber', '2026-10-26 17:05', 75.25),
 (1012, 3, N'Adele Vance', NULL, 410.00);  -- draft

WITH Ranked AS (
    SELECT OrderID, Customer, OrderDate, Total,
           ROW_NUMBER() OVER (
               PARTITION BY CustomerID
               ORDER BY OrderDate DESC, OrderID DESC) AS rn
    FROM dbo.Orders
)
SELECT OrderID, Customer, OrderDate, Total
FROM Ranked
WHERE rn = 1
ORDER BY OrderDate DESC;

Output:

OrderID     Customer         OrderDate           Total       
----------- ---------------- ------------------- ------------
       1011 Alex Wilber      2026-10-26 17:05:00        75.25
       1010 Megan Bowen      2026-10-21 15:45:00       120.00
       1007 Adele Vance      2026-10-14 13:10:00        67.20
       1005 Diego Siciliani  2026-10-02 08:05:00       432.75

Alex now shows 1011 ($75.25); Adele keeps 1007, because NULL sorts as the lowest value. With ASC, that puts NULL first:

-- "Oldest" order: ASC sorts the NULL draft first
SELECT TOP (1) OrderID, OrderDate
FROM dbo.Orders
ORDER BY OrderDate;

Output:

OrderID     OrderDate          
----------- -------------------
       1012                NULL

(1 rows affected)

Draft 1012 shows up as the oldest order; add WHERE OrderDate IS NOT NULL. MIN() and MAX() skip NULLs.

Troubleshooting Latest Record Errors 8120 and 4108

• Error 8120 (“Column ‘dbo.Orders.OrderID’ is invalid in the select list…”): a column next to MAX() isn’t grouped. Use the Step 3 join or ROW_NUMBER().
• Error 4108 (“Windowed functions can only appear in the SELECT or ORDER BY clauses”): compute rn in a CTE, then filter outside.

Which Method Should You Use to Select the Latest Record?

You need	Use	On a tie
Newest row in a table	TOP (1) ... ORDER BY date DESC, id DESC	Highest id wins
Every row at the newest time	TOP (1) WITH TIES	All tied rows
Latest date per group only	MAX() + GROUP BY	No other columns
Latest row per group	ROW_NUMBER() ... rn = 1 in a CTE	One row
Latest rows per group, ties kept	RANK() ... = 1	All tied rows
Per row of a parent table	CROSS/OUTER APPLY (TOP (1))	One row

My default is ROW_NUMBER() with a tie-breaker (ROW_NUMBER with a join).

Where to Take It Next: Date Ranges and Last Modified Records

For the last 7 days, use DATEADD() (month, -6 for six months):

DECLARE @AsOf datetime2(0) = '2026-10-27 08:00';  -- SYSDATETIME() in real code
SELECT OrderID, Customer, OrderDate
FROM dbo.Orders
WHERE OrderDate >= DATEADD(day, -7, @AsOf);

For the last modified record, maintain a ModifiedAt column with an UPDATE trigger in SQL Server and run Step 2 on it; other SQL Server date functions build other windows.

Related reading for ranked queries:

• Count rows per group with a join and GROUP BY
• When a self join is the cleaner answer
• Return a table from a function (APPLY-friendly)
• Why ORDER BY in a view doesn’t sort

Frequently Asked Questions