Can anyone help me to modify the sql code?

Other SQL Server Topics
1

image
image716×551 40.2 KB

select
case when num is null Then'null'
Else num End as num
from MyNumbers
group by num
having count(*) =1
order by num desc
limit 1

Output
num

Expected
num

null

Can anyone tell me why my code is not working? I am not able to show the null value when there is no result is
shown. Thank you very much!

2

I guess 'null' is not the same as null. This is a MS SQL Server forum and limit 1 is not supported by MS SQL; this is how I would solve it with T-SQL:

DROP TABLE IF EXISTS #Numm;

SELECT 8 AS Numm
INTO #Numm
UNION ALL
SELECT 8
UNION ALL
SELECT 3
UNION ALL
SELECT 3
--UNION ALL
--SELECT 1
--UNION ALL
--SELECT 4	
--UNION ALL
--SELECT 5	
--UNION ALL
--SELECT 6;

SELECT * FROM #Numm;

WITH SingleNumber AS
(
SELECT
	Numm, ROW_NUMBER() OVER(ORDER BY Numm DESC) AS RowNumber
FROM #Numm
GROUP BY Numm
HAVING COUNT(*) =1
UNION ALL
SELECT
	NULL, NULL
)
SELECT MAX(Numm) AS Numm
FROM SingleNumber
WHERE RowNumber=1 OR RowNumber IS NULL;
1 Like
3

hi rogier

Great solution

there is no need for this
WHERE RowNumber=1 OR RowNumber IS NULL;

please excuse me i i am wrong

1 Like
4

hi

hope this helps

a different solution from Rogiers

select 
   max(b.num) 
from
   #MyNumbers a 
        left join
  ( select num,count(*) as cnt from #MyNumbers group by num having count(*) = 1 ) b
on a.num = b.num

image

image

1 Like
5

hi

hope this helps

another way of doing this .. a little direct way

select 
    top 1 (case when count(*) > 1 then null else num end)  as ok 
from #MyNumbers 
group by num 
order by ok desc

image

image

1 Like