CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008-r2
Craig
Craig

Reputation: 325

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

Upvotes: 0

Views: 158

Answers (3)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

You need to use LAST_VALUE for your requirement

WITH FAILURES AS
(
    SELECT *, LAST_VALUE(Failure_id) OVER (PARTITION BY url_id ,last_check_success ORDER BY Request_Start) AS lastFail
    FROM URL_FAILURES
)

Upvotes: 1

Jun Yuan
Jun Yuan

Reputation: 414

Try this: WITH FAILURES AS ( SELECT url_id, request_start, last_check_success, last_value(failure_id) OVER (PARTITION BY last_check_success ORDER BY request_start) FROM URL_FAILURES )

Upvotes: 1

Kostya
Kostya

Reputation: 1605

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
    )

Upvotes: 1

Related Questions