Top 1 not return null

  • I want to select top 1 value either null or value from this query

    select top 1 column5 from table2 where table2.column1ID = 5

    if the whole value of this column is null the return is empty , but I need to return value or null

  • ali.m.habib (10/10/2013)


    if the whole value of this column is null the return is empty , but I need to return value or null

    If the value of this column is null, you need to return null or value?

    Mission accomplished I'd guess, but I think you mean that if there is no column1ID equal to 5 you need to return NULL.

    edit: query I proposed didn't yield correct result in all cases. Working on it...

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Found a query that works:

    WITH CTE_ResultSet AS

    (

    SELECT [column5] FROM [dbo].[table2] WHERE [column1ID] = 5

    UNION

    SELECT NULL

    )

    SELECT [column5] FROM

    (SELECT [column5], ROW_NUMBER() OVER(ORDER BY [column5] DESC) AS RID FROM CTE_ResultSet) tmp

    WHERE RID = 1

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • ali.m.habib (10/10/2013)


    I want to select top 1 value either null or value from this query

    select top 1 column5 from table2 where table2.column1ID = 5

    if the whole value of this column is null the return is empty , but I need to return value or null

    How about

    With criteria AS (

    SELECT 5 AS column1ID)

    SELECT top 1 column5 from

    criteria c

    LEFT OUTER JOIN table2 t ON c.column1ID = t.column1ID

  • Here is another way without using the ROW_NUMBER window function.

    select top 1 column5 from

    (

    SELECT [column5], 1 as SortOrder FROM [dbo].[table2] WHERE [column1ID] = 5

    UNION

    SELECT NULL, 2

    )x order by SortOrder

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (10/10/2013)


    Here is another way without using the ROW_NUMBER window function.

    select top 1 column5 from

    (

    SELECT [column5], 1 as SortOrder FROM [dbo].[table2] WHERE [column1ID] = 5

    UNION

    SELECT NULL, 2

    )x order by SortOrder

    Tried it as well, but somehow managed to get a different result (see first reply where I deleted my query).

    Must've messed it up somehow.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • maybe you were missing the order by? All the solutions seem workable to me.

  • ali.m.habib (10/10/2013)


    I want to select top 1 value either null or value from this query

    select top 1 column5 from table2 where table2.column1ID = 5

    if the whole value of this column is null the return is empty , but I need to return value or null

    SELECT (select top 1 column5 from table2 where table2.column1ID = 5 order by columnID) as column5

    (Added the order by because I don't like to see TOP without ORDER BY ever)

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • mister.magoo (10/10/2013)


    ali.m.habib (10/10/2013)


    I want to select top 1 value either null or value from this query

    select top 1 column5 from table2 where table2.column1ID = 5

    if the whole value of this column is null the return is empty , but I need to return value or null

    SELECT (select top 1 column5 from table2 where table2.column1ID = 5 order by columnID) as column5

    (Added the order by because I don't like to see TOP without ORDER BY ever)

    Well, it seems you win the prize of the shortest statement 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (10/10/2013)


    Well, it seems you win the prize of the shortest statement 🙂

    I thank you 😉

    Not the first time I've won a prize for the shortest one....:cool:

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

    Viewing 10 posts - 1 through 9 (of 9 total)

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