Oracle SQL: Get Max Date Row Per UserId (Latest Record)

In Oracle SQL, the most efficient way to fetch rows with the maximum Date for each distinct UserId is using a window function like ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC), then filtering where the row number is 1—this grabs the latest record per UserId, including UserId and Value. You’ll get every UserId represented exactly once, with its most recent Date’s full row. Alternatives like JOIN with a subquery or NOT EXISTS work too, but window functions shine for performance on large tables.

Contents

• Understanding the Problem
• Method 1: ROW_NUMBER() Window Function
• Method 2: JOIN with Subquery
• Method 3: NOT EXISTS Pattern
• Oracle-Specific: KEEP (DENSE_RANK LAST)
• Handling Ties and Edge Cases
• Performance Tips
• Sources
• Conclusion

Understanding the Problem

Picture this: you’ve got a table with UserId, Value, and Date columns. Multiple rows per UserId, each with different Dates and Values. A simple GROUP BY UserId with MAX(Date) spits out just the max date—but you lose Value, and it’s not the full row. What you need is the entire row for that max Date per UserId. Every distinct UserId shows up once, with its latest snapshot.

Why does this trip people up? Basic aggregates like MAX don’t carry other columns along for the ride. Enter window functions and clever joins—they’re Oracle SQL staples for “latest record per group” queries. We’ve all been there, staring at duplicate UserIds wondering which Value to pick.

Method 1: ROW_NUMBER() Window Function

ROW_NUMBER() is your go-to in modern Oracle SQL. It numbers rows within partitions—here, per UserId—ordered by Date descending. Row 1? That’s your max Date row.

SELECT UserId, Value, Date
FROM (
 SELECT UserId, Value, Date,
 ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) AS rn
 FROM your_table
) ranked
WHERE rn = 1;

Boom. Handles all UserIds, pulls Value from the exact max Date row. Stack Overflow users swear by this for its speed and simplicity. Need ties? Swap to RANK() later.

Test it on sample data:

• UserId 1, Value ‘A’, Date 2023-01-01 → out
• UserId 1, Value ‘B’, Date 2023-01-02 → picked (latest)

Short. Clean. Scales.

Method 2: JOIN with Subquery

Old-school but rock-solid: subquery finds max Date per UserId, then JOIN back to grab Value and full details. No windows needed.

SELECT t.UserId, t.Value, t.Date
FROM your_table t
JOIN (
 SELECT UserId, MAX(Date) AS max_date
 FROM your_table
 GROUP BY UserId
) m ON t.UserId = m.UserId AND t.Date = m.max_date;

This mirrors real-world fixes on Database Administrators Stack Exchange. What if ties exist (same max Date)? It returns all matching rows per UserId. Want one? Add more ORDER BY in a window version.

Pro: Intuitive if you’re from GROUP BY land. Con: Subquery scans twice. Still crushes on smaller sets.

Method 3: NOT EXISTS Pattern

Think “anti-join.” Pick rows where no later Date exists for that UserId. Sneaky, efficient.

SELECT UserId, Value, Date
FROM your_table t1
WHERE NOT EXISTS (
 SELECT 1
 FROM your_table t2
 WHERE t2.UserId = t1.UserId
 AND t2.Date > t1.Date
);

Stack Overflow has gems like this—no subquery aggregates, pure logic. Great for indexes on (UserId, Date DESC). Downside? Correlated subquery can lag on massive tables without tuning.

And yeah, it naturally picks the latest per UserId. Pair with DISTINCT if paranoid about dups.

Oracle-Specific: KEEP (DENSE_RANK LAST)

Oracle flex: aggregate functions with KEEP grab values from the “last” row in an order.

SELECT UserId,
 MAX(Value) KEEP (DENSE_RANK LAST ORDER BY Date) AS Value,
 MAX(Date) AS Date
FROM your_table
GROUP BY UserId;

This Stack Overflow thread nails it. No subquery, pure GROUP BY magic. Value pulls from the max Date row automatically. Oracle 11g+ loves it—compact for reports.

But heads up: only works if Value is aggregatable (no complex types). Window functions edge it for flexibility.

Handling Ties and Edge Cases

What if two rows share the max Date for a UserId? ROW_NUMBER() picks one (arbitrarily unless you tie-break). RANK() or DENSE_RANK() return all ties.

Tweak ROW_NUMBER():

ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC, Value ASC)

Picks lowest Value on tie.

NULL Dates? Filter WHERE Date IS NOT NULL. Empty table? Zero rows, as expected.

TechEmpower-style benchmarks show windows win on ties. Real talk: test your data.

Performance Tips

Index (UserId, Date DESC) first—query planner’s dream. Windows beat correlated subs on big data; EXPLAIN PLAN to confirm.

For millions of rows, CTEs clean it up:

WITH ranked AS (
 SELECT *, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) rn
 FROM your_table
)
SELECT UserId, Value, Date FROM ranked WHERE rn = 1;

Database Star breaks it down. Avoid SELECT * in prod—name columns. Parallel hint if clustered?

In 2026 Oracle, these fly on Exadata. Your mileage varies by stats.

Sources

1. ORACLE SQL find row with max date for each grouping - Stack Overflow
2. Taking the record with the max date - Stack Overflow
3. Select which has max date or latest date - Database Administrators Stack Exchange
4. In SQL how do I select only rows with max value on a column? - Sentry
5. SQL get LAST record for each ID after some Date - Stack Overflow
6. Select latest row for each group from oracle - Stack Overflow
7. GROUP BY with MAX(DATE) - Stack Overflow
8. SQL- How to get last record for each account - Stack Overflow
9. How to Select Rows with Max Value for a Column in Oracle SQL - Database Star
10. Sql: Retrieving the latest record for every ID in SQL - CopyProgramming

Conclusion

For Oracle SQL max date rows per UserId, start with ROW_NUMBER()—it’s versatile, fast, and gets your latest Value every time. JOIN subqueries suit beginners; KEEP for aggregate fans. Index smart, test ties, and you’re golden. Pick based on your table size; windows rarely disappoint.