SQL MAX Date: Get Latest Row with Examples

When working with SQL, one common task is to get the most recent record based on a date column. Whether it’s the latest order, last login, or newest update, you’ll likely use the SQL MAX() function.

To help you follow along, we’ll first create a sample table and seed it with data.

We’ll use a simple orders table with the following columns:

• id: primary key
• customer_id: ID of the customer
• order_date: date the order was placed
• amount: order total
• updated_at: last time the row was updated

SQL to Create the Table:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  amount DECIMAL(10, 2),
  updated_at TIMESTAMP
);

SQL to Seed the Table with Data:

INSERT INTO orders (id, customer_id, order_date, amount, updated_at) VALUES
(1, 101, '2024-05-01', 100.00, '2024-05-01 09:00:00'),
(2, 101, '2024-05-10', 150.00, '2024-05-10 14:30:00'),
(3, 102, '2024-05-03', 200.00, '2024-05-03 11:45:00'),
(4, 103, '2024-05-05', 300.00, '2024-05-05 16:10:00'),
(5, 102, '2024-05-18', 120.00, '2024-05-18 10:20:00'),
(6, 103, '2024-05-20', 220.00, '2024-05-20 17:15:00'),
(7, 101, '2024-05-20', 175.00, '2024-05-20 18:00:00');

This sample data allows us to work with real use cases when querying for the latest order.

To get the most recent order_date from the table, use the MAX() function:

SELECT MAX(order_date) AS latest_order_date FROM orders;

Expected Result:

Explanation:

Returns the most recent date in the order_date column.

To get the entire row for the latest order(s), use a subquery:

SELECT *
FROM orders
WHERE order_date = (SELECT MAX(order_date) FROM orders);

Expected Result:

Explanation:

This returns all rows from orders where the order_date is equal to the latest date.
If multiple orders were made on the latest date, all will be shown.

This is helpful when you want the full details of the most recent entry.

If you want only one most recent row, and don’t care about ties, use ORDER BY with LIMIT 1:

SELECT *
FROM orders
ORDER BY order_date DESC
LIMIT 1;

Expected Result:

Explanation:

This sorts the orders by date in descending order, then returns just the top one.

To get the most recent order date per customer, use GROUP BY:

SELECT customer_id, MAX(order_date) AS latest_order_date
FROM orders
GROUP BY customer_id;

Expected Result:

Explanation:

This groups the data by customer_id and shows each customer’s most recent order date.

This is useful for reports like “last purchase date per customer.”

To return the complete order details for the most recent order of each customer, use ROW_NUMBER() window function in a CTE.

WITH ranked_orders AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC
         ) AS rn
  FROM orders
)
SELECT *
FROM ranked_orders
WHERE rn = 1;

Expected Result:

Explanation:

• The ROW_NUMBER() function ranks each order within its customer group.
• Orders are sorted by order_date in descending order.
• Then we filter the rows to keep only the first (most recent) one per customer.

If you have a column like updated_at, you can use similar logic:

SELECT *
FROM orders
WHERE updated_at = (SELECT MAX(updated_at) FROM orders);

Expected Result:

Explanation:

Returns the full row with the most recent update time.

• Use MAX() to get the latest date value from a column.
• Use a subquery to get the full row for that date.
• Use GROUP BY with MAX() to find the latest date per group.
• Use ROW_NUMBER() to return full details for the latest record per group.
• Use ORDER BY ... LIMIT 1 for the single latest row by date.

Finally, if you found it useful, you can check more here:

• How to Inner Join 3 Tables in SQL
• Facade vs Proxy vs Adapter Design Patterns
• TypeScript Type Vs Interface? The Answer Is Type!