How to use WHERE with virtual column name added with UNION?

Question

SELECT '1000000' AS number
UNION ALL
SELECT '541' AS number
UNION ALL
SELECT '-500' AS number
UNION ALL
SELECT '100' AS number
UNION ALL
SELECT number, 'biggest' AS result
WHERE number = 1000000

How to make this work? My task is to find the biggest number from those that I added.

SELECT '1000000' AS number
UNION ALL
SELECT '541' AS number
UNION ALL
SELECT '-500' AS number
UNION ALL
SELECT '100' AS number
UNION ALL
SELECT number, 'biggest' AS result
WHERE number = 1000000

How to make this work? My task is to find the biggest number from those that I added.

score 1 · Answer 1 · answered Nov 11 '22 at 12:15

You need to wrap the query in a derived table to be able access a column alias

select *
from (
  SELECT 1000000 AS number
  UNION ALL
  SELECT 541 AS number
  UNION ALL
  SELECT -500 AS number
  UNION ALL
  SELECT 100 AS number
)
WHERE number = 1000000

But to find the highest number, you can use an order by and limit:

select *
from (
  SELECT 1000000 AS number
  UNION ALL
  SELECT 541 AS number
  UNION ALL
  SELECT -500 AS number
  UNION ALL
  SELECT 100 AS number
)
order by number desc 
limit 1

Alternatively you can also simplify this by using a VALUES clause:

select *
from (
  values (1000000),(541),(-500),(100)
) as t(number)
order by number desc 
limit 1

@Bergi: absolutely, yes ;) I was more focusing on the WHERE part — , Nov 11 '22 at 12:56

score 0 · Answer 2 · answered Nov 11 '22 at 12:18

you can use common table expresssion with FETCH as given below:

WITH CTE_Number AS (
SELECT 1000000 AS number
UNION ALL
SELECT 541 AS number
UNION ALL
SELECT -500 AS number
UNION ALL
SELECT 100 AS number
    )
SELECT Number,'Biggest' as result
FROM CTE_Number
ORDER BY Number DESC
FETCH FIRST ROW ONLY;

How to use WHERE with virtual column name added with UNION?

2 Answers2