Sean Lange (8/29/2013)
Koen Verbeeck (8/29/2013)
WITH CTE_NoZero AS(SELECT ID, ReadNo, deviceNo FROM DeviceReading WHERE ReadNo <> 0)
SELECT ReadNo, deviceNo
FROM
(SELECT ReadNo, deviceNo, RID = ROW_NUMBER() OVER (PARTITION BY deviceNo ORDER BY ID DESC)
FROM CTE_NoZero) tmp
WHERE RID = 1;
Isn't that going to return the same thing as the version I posted?
For this sample data, yes. But what if ID 11 has 30 instead of 80. Your query would return ID 6 with 50 (maximum value), while mine would still return ID 11 with value 30 (last non-zero value).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP