Post reply

Sort, how to treat NULL as Max value

  • January 31, 2014 at 4:18 pm

    #287878

    Hi,

    I want to select single row based on value in toDD, if NULL then take this one, if NOT then get with max date:

    Is there any smart trick to do this using same sql, I can probably do replace isnull, etc....??

    WITH t1 AS (

    SELECT '9999999' id,'2013-10-01' FromDD, NULL ToDD UNION

    SELECT '3333333' id,'2013-6-01' FromDD, '2013-07-01' ToDD UNION

    SELECT '1111111' id,'2013-1-01' FromDD, '2013-05-30' ToDD

    )

    -- need id=999999 in case above,

    select TOP 1 * FROM t1 ORDER BY toDD desc

    WITH t2 AS (

    SELECT '3333333' id,'2013-6-01' FromDD, '2013-07-01' ToDD UNION

    SELECT '1111111' id,'2013-1-01' FromDD, '2013-05-30' ToDD

    )

    -- need id=333333

    select TOP 1 * FROM t1 ORDER BY toDD desc ----????

    [/code]

    Thanks

    Mario

  • January 31, 2014 at 4:29 pm

    #1685254

    Maybe something as simple as changing the NULL to a value as '9999-01-01'. It would be better if you do this in your table to avoid functions and possibly use indexes. But you could do it in your query if the previous option is not viable due to other reasons.

    select TOP 1 * FROM t1 ORDER BY ISNULL(toDD, '9999-01-01') desc;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 31, 2014 at 4:31 pm

    #1685255

    Fantastico, Tx Luis

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply