Selecting a row with specific value from a group?

Question

From the table below, if Project.Date group has a Fail and Success, I'd like to keep the Fail row, but if single row (like the rest,) then keep that row regardless of Status. For example I'd like to keep the first row, discard the second and keep the rest on the table below.

╔═════════╦══════════╦═════════╗
║ PROJECT ║   DATE   ║ STATUS  ║
╠═════════╬══════════╬═════════╣
║ HLM     ║ 20130422 ║ Fail    ║
║ HLM     ║ 20130422 ║ Success ║
║ HLM     ║ 20130423 ║ Fail    ║
║ HLM     ║ 20130424 ║ Success ║
║ HLM     ║ 20130425 ║ Fail    ║
║ HLM     ║ 20130426 ║ Success ║
╚═════════╩══════════╩═════════╝

score 3 · Accepted Answer · answered Apr 28 '13 at 16:34

WITH records
AS
(
    SELECT  [Project], [Date], [Status],
            ROW_NUMBER() OVER (PARTITION BY [Project], [Date]
                                ORDER BY [Status]) rn
    FROM    TableName
)
SELECT  [Project], [Date], [Status]
FROM    records
WHERE   rn = 1

SQLFiddle Demo

OUTPUT

╔═════════╦══════════╦═════════╗
║ PROJECT ║   DATE   ║ STATUS  ║
╠═════════╬══════════╬═════════╣
║ HLM     ║ 20130422 ║ Fail    ║
║ HLM     ║ 20130423 ║ Fail    ║
║ HLM     ║ 20130424 ║ Success ║
║ HLM     ║ 20130425 ║ Fail    ║
║ HLM     ║ 20130426 ║ Success ║
╚═════════╩══════════╩═════════╝

Selecting a row with specific value from a group?

1 Answers1