Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Top 1 not return null Expand / Collapse
Author
Message
Posted Thursday, October 10, 2013 2:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 30, 2015 2:09 AM
Points: 64, Visits: 154
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
Post #1503417
Posted Thursday, October 10, 2013 3:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:59 AM
Points: 14,513, Visits: 12,555
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...




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1503424
Posted Thursday, October 10, 2013 3:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:59 AM
Points: 14,513, Visits: 12,555
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





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1503431
Posted Thursday, October 10, 2013 7:31 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 2, 2015 4:09 PM
Points: 939, Visits: 3,142
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

Post #1503569
Posted Thursday, October 10, 2013 8:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 14,343, Visits: 14,081
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1503614
Posted Thursday, October 10, 2013 10:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:59 AM
Points: 14,513, Visits: 12,555
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1503690
Posted Thursday, October 10, 2013 11:33 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 2, 2015 4:09 PM
Points: 939, Visits: 3,142
maybe you were missing the order by? All the solutions seem workable to me.
Post #1503713
Posted Thursday, October 10, 2013 12:56 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 1,974, Visits: 6,667
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
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw
  • Post #1503751
    Posted Thursday, October 10, 2013 1:51 PM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Yesterday @ 6:59 AM
    Points: 14,513, Visits: 12,555
    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




    How to post forum questions.
    Need an answer? No, you need a question.
    What’s the deal with Excel & SSIS?

    Member of LinkedIn. My blog at SQLKover.

    MCSA SQL Server 2012 - MCSE Business Intelligence
    Post #1503776
    Posted Thursday, October 10, 2013 3:49 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 4:54 PM
    Points: 1,974, Visits: 6,667
    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....


    MM


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


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw
  • Post #1503814
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse