SQL - error when using UNION

Question

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?

http://stackoverflow.com/questions/5551064/combining-order-by-and-union-in-sql-server duplicate. Cannot combine order by with union but using multple ctes, subqueries, or cte with window functions can get you the answer. Also curious what if 2 products have the exact same expensive or cheap price would you want more than 1 result? — Matt, Jun 26 '16 at 18:19
Sorry for duplicate question. If most expensive had the same price as the cheapest I would like to see both results — mnj, Jun 26 '16 at 18:30
no worries some times it is hard to figure out terminology to search for. My question of 2 products is actually slightly different than your eample lets say you have 2 products that have the same cheap price and 2 different products that have the same expensive price do you want all 4 products or just 1 of each? Because if you want all 4 products you need to my answer with the window functions below. not the other methods. — Matt, Jun 26 '16 at 18:35
That's good to know. I think I could also use WITH TIES in order to display all products with the same prices. Actually, I'll try to see it that would work — mnj, Jun 26 '16 at 18:38
I just tried that and it worked. Again, thank you for your help. — mnj, Jun 26 '16 at 18:45

score 2 · Accepted Answer · answered Jun 26 '16 at 18:14

2

;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

answered Jun 26 '16 at 18:14

M.Ali

67,945
13
101
127

Good answer. It's also a good idea to add WITH TIES to be able to see if there are more products with the same price (for example 2 cheapes products) – mnj Jun 26 '16 at 18:47

Matt · Answer 2 · 2016-06-26T18:38:45.703

2

As Sub Select

SELECT *
FROM
    (
    SELECT *
       ,MinPriceRowNum = ROW_NUMBER() OVER (ORDER BY Start_Dt)
       ,MaxPriceRowNum = ROW_NUMBER() OVER (ORDER BY Start_Dt DESC)
       ,MinPriceRank = DENSE_RANK() OVER (ORDER BY Start_Dt)
       ,MaxPriceRank = DENSE_RANK() OVER (ORDER BY Start_Dt DESC)
    FROM    
       #Table
    ) t
WHERE
    MinPriceRowNum = 1
    OR MaxPriceRowNum = 1

AS Common Table Expression cte

;WITH cte AS (
    SELECT *
       ,MinPriceRowNum = ROW_NUMBER() OVER (ORDER BY UnitPrice)
       ,MaxPriceRowNum = ROW_NUMBER() OVER (ORDER BY UnitPriceDESC)
       ,MinPriceRank = DENSE_RANK() OVER (ORDER BY UnitPrice)
       ,MaxPriceRank = DENSE_RANK() OVER (ORDER BY UnitPriceDESC)
    FROM    
       TableName
)

SELECT *
FROM
    cte
WHERE
    MinPriceRowNum = 1
    OR MaxPriceRowNum = 1

If you want to see all ties for max and min switch to MinPriceRank = 1 OR MaxPriceRank = 1. If only 1 row desired stay with the RowNum columns.

edited Jun 26 '16 at 18:38

answered Jun 26 '16 at 18:24

Matt

13,833
2
16
28

Your answer is good, but I checked the other one as correct answer, because the syntax you used is more complicated and, to be honest, I don't fully understand it. I only know the basics of SQL. Anyway, thanks for your help. – mnj Jun 26 '16 at 18:36
I was using window functions and common table expressions. Both will be assets for you to put in your sql toolbox – Matt Jun 26 '16 at 18:42
Unfortunately I just finished my SQL course at university, but I hope I'll have an opportunity to learn some more, because it seems to be more interesting than I expected. – mnj Jun 26 '16 at 18:49

score 1 · Answer 3 · answered Jun 26 '16 at 18:11

1

You can use round brackets like this:

(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)

Cheers!

answered Jun 26 '16 at 18:11

Niek van Raaij

19
3

Unfortunately it didn't work, but thanks for trying to help – mnj Jun 26 '16 at 18:27

SQL - error when using UNION

3 Answers3