Example table:
| User | Updated | Item | Status |
|---|---|---|---|
| Bob | 1.4 | Apple | Eaten |
| Bob | 1.4 | Orange | Moldy |
| Bob | 1.3 | Apple | Ripe |
| Bob | 1.2 | Apple | New |
| Bob | 1.1 | Orange | Ripe |
| Bob | 1.0 | Banana | New |
I want to be able to search based on a given Updated value and fetch each unique User/Item row matching the given value, as well as the next highest Updated row, in order to track historical changes in Item values.
If there is only one row, don’t return it.
For example, doing a search for Bob and 1.4 should return:
| User | Updated | Item | Status |
|---|---|---|---|
| Bob | 1.4 | Apple | Eaten |
| Bob | 1.3 | Apple | Ripe |
| Bob | 1.4 | Orange | Moldy |
| Bob | 1.1 | Orange | Ripe |
Here, the matching Updated rows (1.4) for both Apple and Orange are included, as well as the next closest row (1.3 and 1.1 respectively).
This is where I’m having issues; if it was simply to return matching Updated and Updated-0.1 the query would be straightforward, but I’m looking for the next highest Updated column match for each.
2
WITH RankedUpdates AS (
SELECT
User,
Updated,
Item,
Status,
ROW_NUMBER() OVER (PARTITION BY User ORDER BY Updated DESC) AS rn
FROM
YourTableName
WHERE
User = 'Bob' AND Updated <= 1.4
)
SELECT *
FROM RankedUpdates
WHERE rn <= 2 -- Get the current and the next highest
ORDER BY Updated DESC;
Output:
| User | Updated | Item | Status |
|---|---|---|---|
| Bob | 1.4 | Apple | Eaten |
| Bob | 1.4 | Orange | Moldy |
| Bob | 1.3 | Apple | Ripe |
Silver Spade is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1