By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test (num int);
insert into test values (8),(8),(7),(7),(3),(3);
select * from test;
create table test2 (num int);
insert into test2 values (8),(8),(3),(3),(1),(4),(5),(6);
select * from test2;
| num |
|---|
| 8 |
| 8 |
| 7 |
| 7 |
| 3 |
| 3 |
| num |
|---|
| 8 |
| 8 |
| 3 |
| 3 |
| 1 |
| 4 |
| 5 |
| 6 |
select num numg from test group by num having count(*)=1
| numg |
|---|
select max(numg) num
from( select num numg from test group by num having count(*)=1 )a
| num |
|---|
| null |
select max(numg) num
from(
select num numg from test2 group by num having count(*)=1
)a
| num |
|---|
| 6 |
WITH CTE AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY num ORDER BY num) AS RN
FROM test MyNumbers
)
select * from cte;
| num | RN |
|---|---|
| 3 | 1 |
| 3 | 2 |
| 7 | 1 |
| 7 | 2 |
| 8 | 1 |
| 8 | 2 |
WITH CTE AS
(
SELECT *,
count(*) OVER (PARTITION BY num ORDER BY num) AS cnt
FROM test MyNumbers
),
CTE2 AS
(
SELECT TOP 1 num
FROM CTE
WHERE cnt=1
)
select max(num) maxNum from cte2
| maxNum |
|---|
| null |
WITH CTE AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY num ORDER BY num) AS RN
FROM test2 MyNumbers
),
CTE2 AS
(
SELECT TOP 1 num
FROM CTE
WHERE num NOT IN (SELECT num FROM CTE WHERE RN >= 2)
ORDER BY num DESC
)
SELECT max(CASE
WHEN num IS NULL OR num = ''
THEN NULL
ELSE num
END) AS num FROM CTE2;
| num |
|---|
| 6 |