Null Or MAX

  • 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

  • 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

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

     

     

  • 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