Loreno Loreno - 5 months ago 10
SQL Question

SQL - error when using UNION

Here's my query:

Select top 1 ProductName, UnitPrice from Products
order by UnitPrice desc

UNION

Select top 1 ProductName, UnitPrice from Products
Where UnitPrice > 0
order by UnitPrice asc


I wanted to display the most expensive and the cheapest products in one table. That's why I used UNION to join the two queries. Unfortunately I get an error


Incorrect syntax near the keyword 'UNION'


That's why I'm asking for your help - what's wrong with my query?

Answer
;WITH X AS 
  (
    Select top 1 ProductName, UnitPrice from Products
    order by UnitPrice desc
  ), 
 Y AS 
  (
    Select top 1 ProductName, UnitPrice from Products
    Where UnitPrice > 0
    order by UnitPrice asc 
)
SELECT ProductName, UnitPrice FROM X
UNION ALL
SELECT ProductName, UnitPrice FROM Y