I'm unsure how to both group and select the last value from within that group and could use some expertise.
This simple table (snippet of table below) holds the values of various timestamps for when a series of URLs I'm checking don't respond correctly. The yellow highlighted lines represent the data that I'm attempting to capture.
Essentially I'm trying to capture the last downtime timestamp for each grouping. failure_id will auto-increment with each entry.
I've got a partial success with the following:
WITH FAILURES AS
SELECT *, ROW_NUMBER() OVER (PARTITION BY url_id ORDER BY failure_id DESC) AS lastFail
lastFail = 1
Microsoft SQL Server 2008 R2 (SP3)
Database Compatibility: SQL Server 2005
WITH FAILURES AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY url_id ,last_check_success ORDER BY failure_id DESC) AS lastFail FROM URL_FAILURES )