CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
cs0815
cs0815

Reputation: 17388

get the first entry where value is not null SQL Server 2012

I have a table with these 3 columns:

value date id

For each id i would like to determine the first value using date to order. Is this a valid approach?:

    ;with cte as
    (
        select 
            id
            ,date
            ,value
            ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY date asc) AS RN 
        from Bla
where value is not null
    )
    select 
        * 
    from cte
    where RN = 1

Upvotes: 0

Views: 94

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

Another approach would be to use TOP 1 WITH TIES, ordering the records like this:

SELECT TOP 1 WITH TIES id, [date], [value]
FROM BLA
ORDER BY ROW_NUMBER() OVER(PARTITION BY ID ORDER BY 
    CASE WHEN [value] IS NULL THEN '2525-12-31' ELSE [date] END)

Upvotes: 1

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

Yes, that is a valid approach. I would do it the same way, maybe without the asterisk and without the semicolon in the beginning (since I also don't use a full stop to begin a sentence):

WITH 
  cte (id, [date], [value], RN) AS (
    SELECT id, [date], [value],
      ROW_NUMBER() OVER (PARTITION BY id ORDER BY [date])
    FROM Bla
    WHERE [value] IS NOT NULL
  )
SELECT id, [date], [value]
FROM cte
WHERE RN = 1;

Upvotes: 1

Related Questions