Craig Craig - 2 months ago 6
SQL Question

Select last value from within multiple groupings (SQL Server)

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.

Objective

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
FROM URL_FAILURES
)


and then building my actual SELECT statement with that data and the condition

WHERE
lastFail = 1


...but it disregards the groupings that I need for last_check_success.




Microsoft SQL Server 2008 R2 (SP3)
Database Compatibility: SQL Server 2005

Answer

try this

   WITH FAILURES AS
    (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY url_id ,last_check_success ORDER BY failure_id DESC) AS lastFail
        FROM URL_FAILURES
    )
Comments