Print the latest non-null entry

Question

I am trying to retrieve the most recent entry and find a non- NULL match. The problem is that 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

Select the last 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

Answer 1

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 fiddle .

Answer 2

 ;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

Answer 3

The query in SQLFiddle does not work due to a subquery returning more than 1 row.
Adding TOP 1 fixes this. Check if everything is OK.

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