Last_Value returning multiple records

Question

I have a table (SROEnquiry) as listed in the snapshot below: Data.jpg

I’m using Last_Value() to get the last entry of each StudentNumberstudent on the table. StudentNumber 'Y1293926' is used as example.

The script is as follows:

SELECT DISTINCT(StudentNumber),
       LAST_VALUE(Arrears) OVER 
       (PARTITION BY StudentNumber ORDER BY RecordID) AS LastValue  
FROM SROEnquiry 
WHERE StudentNumber = 'Y1293926'

The result is as follows:

The expected result is:

StudentNumber   LastValue
Y1293926        0.00

What is wrong with the script that it brings back 4 values instead of only one?

Answer 1

You need to specify the correct frame with LAST_VALUE:

SELECT DISTINCT
    StudentNumber,
    LAST_VALUE(Arrears) OVER (PARTITION BY StudentNumber ORDER BY RecordID
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LastValue  
FROM SROEnquiry 
WHERE StudentNumber = 'Y1293926'

Answer 2

LAST VALUE returns the last value of the window created by the OVER clause. The window expands while records are being ordered by RecordID, hence LAST VALUE fetches a different value each time.

Also, DISTINCT is applied to both fields of the SELECT clause. Hence you get one record for each value returned by LAST VALUE.

Why not just use:

SELECT TOP 1 StudentNumber, Arrears AS LastValue  
FROM SROEnquiry 
WHERE StudentNumber = 'Y1293926'
ORDER BY RecordID DESC