2

I have this table.


ID       Date       Value 
___      ____       _____
3241     9/17/12    5
3241     9/16/12    100
3241     9/15/12    20
4355     9/16/12    12
4355     9/15/12    132
4355     9/14/12    4
1001     NULL       89
1001     9/16/12    125
5555     NULL       89
1234     9/16/12    45
2236     9/15/12    128
2236     9/14/12    323
2002     9/17/12    45

I would like to select the maximum date grouped by id and including NULL as maximum value that should be in the result to get something like that.


ID       Date       Value 
___      ____       _____
3241     9/17/12    5
4355     9/16/12    12
1001     9/16/12    125
5555     NULL       89
1234     9/16/12    45
2236     9/15/12    128
2002     9/17/12    45

I found a solution but that not include NULL as maximum value the solution by @bluefeet Return value at max date for a particular id


SELECT t1.id,
       t2.mxdate,
       t1.value
FROM yourtable t1
INNER JOIN
  ( SELECT max(date) mxdate,
           id
   FROM yourtable
   GROUP BY id) t2 ON t1.id = t2.id
AND t1.date = t2.mxdate

I also search for a solution to see how can we select NULL maximum value in t-sql so i found this solution by @Damien_The_Unbeliever How can I include null values in a MIN or MAX?


SELECT recordid,
       MIN(startdate),
       CASE
           WHEN MAX(CASE
                        WHEN enddate IS NULL THEN 1
                        ELSE 0
                    END) = 0 THEN MAX(enddate)
       END
FROM tmp
GROUP BY recordid

But i m stuck i don't know how to merge between this two solution to get what i want.

PS: I m using SQL SERVER 2008

Hicham Bouchilkhi
  • 682
  • 10
  • 29
  • You sample data does not contain any ID with both a date record and a null date record. Which would you like to select in that case; the record with the date or the record with null? – Thorsten Kettner Jun 26 '17 at 08:55
  • i didn't put this case in my data because i m talking about the maximum value so if there's a date and a null by default it's the date – Hicham Bouchilkhi Jun 26 '17 at 09:03
  • 1
    Okay. The accepted solution, however, prefers the null over the date (because "including NULL as maximum value" sounds like you want it thus). Change `ISNULL([Date],'9999-12-31')` to `ISNULL([Date],'0001-01-01')` to give the dates precedence over null. – Thorsten Kettner Jun 26 '17 at 10:54
  • Oh i see what do you want to mean you're right – Hicham Bouchilkhi Jun 26 '17 at 11:58

1 Answers1

2

You can use this

SELECT
ID
,[Date]
,[Value]
FROM(
SELECT 
* 
, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ISNULL([Date],'9999-12-31') DESC) AS Row#
FROM yourtable
) A WHERE Row# = 1
Tien Nguyen Ngoc
  • 1,555
  • 1
  • 8
  • 17