CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Kermit
Kermit

Reputation: 34054

fetch most recent non null record

I'm trying to fetch the most recent record and find a non-NULL match. The problem is my subquery returns more than one result.

Data set

| ID |         DD | SIG_ID |      DCRP |
----------------------------------------
|  1 | 2010-06-01 |      1 |    Expert |
|  2 | 2010-09-01 |      1 |    Expert |
|  3 | 2010-12-01 |      1 |    Expert |
|  4 | 2010-12-01 |      1 | Expert II |
|  5 | 2011-03-01 |      1 | Expert II |
|  6 | 2011-06-01 |      1 |    (null) |
|  7 | 2010-06-01 |      2 |    Senior |
|  8 | 2010-09-01 |      2 |    Senior |
|  9 | 2010-09-01 |      2 |    Senior |
| 10 | 2010-12-01 |      2 | Senior II |
| 11 | 2011-03-01 |      2 |    (null) |
| 12 | 2011-03-01 |      2 |    Senior |
| 13 | 2010-06-01 |      3 |    (null) |
| 14 | 2010-09-01 |      3 |    (null) |
| 15 | 2010-12-01 |      3 |    (null) |

Query

SELECT a.sig_id, a.id, 
  CASE
    WHEN b.dcrp IS NULL
      THEN
        (SELECT dcrp
         FROM tbl
         WHERE sig_id = a.sig_id
           AND id < a.id
           AND dcrp IS NOT NULL)
      ELSE b.dcrp
  END AS dcrp
FROM
  (SELECT sig_id, MAX(id) id
  FROM tbl
  GROUP BY sig_id) a
LEFT JOIN
  (SELECT id, dcrp
   FROM tbl
   WHERE dcrp IS NOT NULL) b ON b.id = a.id

Desired result

Fetch the most recent dcrp for each sig_id:

| ID |         DD | SIG_ID |      DCRP |
----------------------------------------
|  5 | 2011-03-01 |      1 | Expert II |
| 12 | 2011-03-01 |      2 |    Senior |
| 15 | 2010-12-01 |      3 |    (null) |

SQL Fiddle

Upvotes: 4

Views: 1276

Answers (3)

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

;with si as (
  select distinct sig_id from tbl
)

select * 
from si
cross apply (select top 1 * from tbl where si.sig_id=tbl.sig_id order by case when dcrp is null then 1 else 0 end asc,dd desc) sii

and with fiddler : http://sqlfiddle.com/#!3/8e267/2/0

Upvotes: 2

Lamak
Lamak

Reputation: 70648

You can use the following:

;WITH CTE AS
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY SIG_ID 
                                ORDER BY CASE WHEN DCRP IS NOT NULL THEN 0 ELSE 1 END,
                                DD DESC) RN
    FROM tbl
)
SELECT *
FROM CTE 
WHERE RN = 1

And the fiddle.

Upvotes: 10

shahkalpesh
shahkalpesh

Reputation: 33474

The query in SQLFiddle fails due to subquery returning more than 1 row.
Adding TOP 1 fixes that. Please check if it is OK.

THEN
        (SELECT TOP 1 dcrp
         FROM tbl
         WHERE sig_id = a.sig_id
           AND id < a.id
           AND dcrp IS NOT NULL)

Upvotes: -2

Related Questions