Question 113794 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search