0

I have a table that contains indexed fields. Could someone help me with an appropriate select statement? This table is kind of like an audit log. When data for a record is changed, a new record is created with up-to-date data, and the current timestamp when that new record is created. I would like to query the most recent entries for a given criteria.

for example:

Base Additive Group Bulk Distance LastUpdated
Aloe Beef 1 910 90 2023-02-01 06:00:00
Aloe Mutton 2 467 533 2023-02-01 06:00:00
Beef Aloe 1 912 90 2023-02-01 06:00:00
Aloe Beef 1 912 90 2023-02-03 07:00:00

In this data, Base and Additive are the indexed fields (no primary key), and there may be multiple records for the pair but distinctly different by the LastUpdated field. I would like my select query to return the last 2 rows in this case as the criteria I would want to supply would be the group number.

1

1 Answer 1

1

If I understand your goal correctly, you're trying to get the latest row per Group. You can achieve this by enumerating the rows with a window function like ROW_NUMBER(), within the partition of the Group field, and sorted by the LastUpdated field descending like so:

DECLARE @GroupFilter INT = 1;

WITH _LatestLogs AS
(
    SELECT 
        Base,
        Additive,
        [Group],
        Bulk,
        Distance,
        LastUpdated,
        ROW_NUMBER() OVER (PARTITION BY Base, Additive, [Group] ORDER BY LastUpdated DESC) AS SortId
    FROM Logs
    WHERE Group = @GroupFilter
)

SELECT 
    Base,
    Additive,
    [Group],
    Bulk,
    Distance,
    LastUpdated
FROM _LatestLogs
WHERE SortId = 1;
7
  • Actually, this doesn't work. It only returns one entry per group. I want to return the latest of all entries in a given group. Commented Feb 6, 2023 at 18:21
  • @WalterZydhek "It only returns one entry per group. I want to return the latest of all entries in a given group." - Can you please clarify the difference?...perhaps by providing an expected output example in your post. "I want to return the latest" typically means 1.
    – J.D.
    Commented Feb 6, 2023 at 18:24
  • I guess my question wasn't clear enough. The example data I show was just a few rows of al the data. I specified I wanted to return the last 2 rows of this data (as shown). Notice both are in group 1, and the 4th row is newer than the first row. Thus, both those last 2 rows should be returned. The 1st row is ignored because it is older than the 4th row, and the 2nd row is ignored because it is not in group 1. Commented Feb 6, 2023 at 18:31
  • @WalterZydhek But the 1st row and 3rd row have the same LastUpdated date, so why is the 3rd row returned when the 1st one is not?...is it because the Base and Additive in row 3 are different from row 4? Would my understanding be correct to say you're looking for the latest row of each combination of Base, Additive for a specific Group at a time?
    – J.D.
    Commented Feb 6, 2023 at 19:33
  • 1
    Ahh! That works. Thank you Commented Feb 6, 2023 at 22:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.