September 28, 2005 at 12:27 pm
Hi
Can anyone please suggest how could this be selected in one single SELECT
"For the same value if there are multiple records then select the one with NULL date if there is one with NULL otherwise select the record with the MAX date"
For example here is the DATA
No Dt
---- ----------
100 01/01/2005
100 02/01/2005/
100 NULL
200 03/01/2005
200 04/01/2005
300 05/01/2005
This is the result I want
No Dt
----- ------------
100 NULL
200 04/01/2005
300 05/01/2005
Thanks for all the help
September 28, 2005 at 12:38 pm
DECLARE @demo table (NO int not null, dt datetime null)
Insert into @demo (NO, dt) values (100, '2005/01/01')
Insert into @demo (NO, dt) values (100, '2005/01/02')
Insert into @demo (NO, dt) values (100, null)
Insert into @demo (NO, dt) values (200, '2005/01/03')
Insert into @demo (NO, dt) values (200, '2005/01/04')
Insert into @demo (NO, dt) values (300, '2005/01/05')
Select NO, NULLIF(MAX(ISNULL(dt, '9999/01/01')), '9999/01/01') as dt from @demo group by no
September 28, 2005 at 1:42 pm
Thank you very much RGR. This definitely helps.
I am having a little problem right now because of the "group by" clause because I am using it in a WHERE clause in a multiple table join. If I won't be able to do it then I might ask for your help again.
Thanks again for your help.
September 28, 2005 at 1:52 pm
Please post the whole select statement.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply