Find the Latest Date for Each Location and Item with SQL
Question details
Return the latest recorded date and corresponding value for each Location and Item combination.

- Product
- SQL database
- Device & OS
- not provided
- Scenario
- A table contains dates and values for multiple locations and items, and the user needs the most recent record for each combination.
- Observed behavior
- The desired result contains each Location and Item combination with its latest date and the value recorded on that date.
Identify the table containing the Location, Item, Value, and Date columns, and check that Date is stored as a date or datetime type. Confirm which SQL dialect your database uses, because identifier quoting may differ.
Use a correlated subquery to find each group's latest date
Use this query when you need the date and value from the latest row for every Location and Item combination.
The subquery finds the maximum date for the current row's Location and Item. The outer query returns rows whose date matches that maximum.
Open your database's SQL query editor and connect to the database containing the table.
Paste this query into the editor, replacing [tablename] with your actual table name: SELECT Location, Item, Value, [Date] FROM [tablename] WHERE [Date] = (SELECT MAX(T.[Date]) FROM [tablename] AS T WHERE T.Location = [tablename].Location AND T.Item = [tablename].Item);
Select Run or Execute, then verify that each Location and Item combination shows its latest date and corresponding value.

Find the latest records in WPS Spreadsheets
Export the database data to a spreadsheet, sort it by date, and remove duplicates by Location and Item to keep one latest record per combination without writing SQL.
- 1. Open the exported data: Export your database table to CSV or XLSX, then use File > Open in WPS Spreadsheets to open it.
- 2. Sort by date: Select the entire data range, open Data > Sort, and sort the Date column from newest to oldest.
- 3. Remove duplicate combinations: With the full data range selected, open Data > Remove Duplicates. Select only Location and Item as the duplicate criteria, then confirm.
- 4. Check the retained rows: Review the resulting Location, Item, Date, and Value columns. Sorting first lets the duplicate-removal operation retain the first, latest row for each combination; tied latest dates result in only one retained row.

Frequently Asked Questions
Why does the query return more than one row for an item?
If rows for the same Location and Item share the maximum date, the WHERE clause matches each of them. Add a tie-breaking rule, such as choosing a unique row ID, if only one row is needed.
Can I use this query in any SQL database?
The correlated-subquery approach is widely supported, but identifier quoting varies. The square brackets shown work in SQL Server and Access; use the quoting required by your database.
What happens if a group has no date?
MAX ignores null dates, and the equality comparison does not match a null result. A Location and Item group containing only null dates will therefore be omitted.
Can WPS Spreadsheets return all rows tied for the latest date?
The Sort and Remove Duplicates method keeps only one row per Location and Item combination. Use the SQL query when you need all rows tied on the latest date.




