Others

Find the Latest Date for Each Location and Item with SQL

John WilsonJohn Wilson Nov 18, 2026 868 views

Question details

Return the latest recorded date and corresponding value for each Location and Item combination.

Find the Latest Date for Each Location and Item with SQL
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.
Before you start

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.

Solution 1Recommended

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.

1
Open a SQL editor

Open your database's SQL query editor and connect to the database containing the table.

2
Enter the query

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);

3
Run and check the results

Select Run or Execute, then verify that each Location and Item combination shows its latest date and corresponding value.

Use a correlated subquery to find each group's latest date
Ties and SQL syntax: If multiple rows share the latest date for a combination, the query returns all of them. Add a tie-breaking rule if you need only one row. Square-bracket quoting works in SQL Server and Access; adjust it for other databases. Groups with only null dates are not returned.
Work with your data in WPS Spreadsheets

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. 1. Open the exported data: Export your database table to CSV or XLSX, then use File > Open in WPS Spreadsheets to open it.
  2. 2. Sort by date: Select the entire data range, open Data > Sort, and sort the Date column from newest to oldest.
  3. 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. 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.
Keep one latest record for each Location and Item combinationOpen and save Microsoft Excel XLSX filesSort and review dates and values in a familiar spreadsheet interface
microsoft office alternative - wps office

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.