Top 1 not return null

• Ali.M.Habib

  SSC Eights!

  Points: 930

  More actions

  October 10, 2013 at 2:52 am

  #283675

  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

• Koen Verbeeck

  SSC Guru

  Points: 259105

  More actions

  October 10, 2013 at 3:13 am

  #1657419

  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

• Koen Verbeeck

  SSC Guru

  Points: 259105

  More actions

  October 10, 2013 at 3:25 am

  #1657421

  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

• Nevyn

  SSChampion

  Points: 14210

  More actions

  October 10, 2013 at 7:31 am

  #1657538

  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

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  October 10, 2013 at 8:22 am

  #1657579

  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/

• Koen Verbeeck

  SSC Guru

  Points: 259105

  More actions

  October 10, 2013 at 10:15 am

  #1657641

  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

• Nevyn

  SSChampion

  Points: 14210

  More actions

  October 10, 2013 at 11:33 am

  #1657660

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

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  October 10, 2013 at 12:56 pm

  #1657695

  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]