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: Sunday, January 5, 2014 5:16 AM
Points: 64, Visits: 153
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: Today @ 8:14 AM
Points: 13,363, Visits: 11,143
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 LessThanDot.

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: Today @ 8:14 AM
Points: 13,363, Visits: 11,143
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 LessThanDot.

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

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 637, Visits: 2,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 @ 6:51 AM
Points: 13,093, Visits: 12,570
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: Today @ 8:14 AM
Points: 13,363, Visits: 11,143
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 LessThanDot.

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

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 637, Visits: 2,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 @ 6:38 PM
Points: 1,796, Visits: 5,804
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


  • MMGrid Addin
  • MMNose Addin


  • 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: Today @ 8:14 AM
    Points: 13,363, Visits: 11,143
    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 LessThanDot.

    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 @ 6:38 PM
    Points: 1,796, Visits: 5,804
    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


  • MMGrid Addin
  • MMNose Addin


  • 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