To get ORDER BY to work over several UNION-ed SELECT statements

Question

After combining several SELECT queries, I can't get ORDER BY to return a result for the highest price in each group

If I add ORDER BY to the end, it shows the results of the tables IN ORDER - I want to return the highest value for each SELECT query. I've tried using UNION ALL between the SELECT's. Also tried adding an ORDER BY to each query (to SELECT each highest value), which doesn't work at all.

SELECT TOP 1 cost_per_unit, season
FROM fruit_imports
WHERE season != 'All Year'
AND season = 'Winter'

UNION 

SELECT TOP 1 cost_per_unit, season
FROM fruit_imports
WHERE season != 'All Year'
AND season = 'Spring'

UNION 

SELECT TOP 1 cost_per_unit, season
FROM fruit_imports
WHERE season != 'All Year'
AND season = 'Summer'

UNION

SELECT TOP 1 cost_per_unit, season
FROM fruit_imports
WHERE season != 'All Year'
AND season = 'Fall'

ORDER BY cost_per_unit DESC

Incorrect syntax near the keyword 'UNION'. is one message I get when using multiple ORDER BY statements. If I use ORDER BY in the 4th SELECT, the result is:

0.22  Winter
0.15  Spring
0.12  Fall
0.02  Summer

The result I want is like:

0.22  Winter
0.60  Spring
0.35  Fall
0.68  Summer

You don't need `UNION` here at all. There are far better ways of achieving this, Have a look at the Duplicate I've marked this as. — Thom A, Aug 17 '19 at 08:41
@Larnu You could at least explain what he is doing incorrectly. You need to provide `ORDER BY` for each part [demo](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=db32e90a762c2ffc196edcad19264b1f). — Lukasz Szozda, Aug 17 '19 at 08:44
But the `OP` doesn't need to provide an `ORDER BY` for each part at all, @LukaszSzozda . They are returning data from the same table 4 times; this is definitely not a case for `UNION`; and can be achieved with a `ROW_NUMBER` far better as it'll only require 1 scan of the table. — Thom A, Aug 17 '19 at 08:48
@Larnu Still it is the part of learning expierience. New users deserve an explanation how to make their query work and then provide better alternatives. Saying at start that sb approach is wrong could discourage new SO users. It is a clear 1 per group scenario. — Lukasz Szozda, Aug 17 '19 at 08:49
If you feel it isn't a duplicate, please vote to reopen @LukaszSzozda . The answers I've linked to do, however, give good explanations as to why they work. — Thom A, Aug 17 '19 at 08:51
@Larnu There is no need to reopen. Just to be a bit more descriptive :) — Lukasz Szozda, Aug 17 '19 at 08:52
No need for a ROW_NUMBER or a UNION, a simple `GROUP BY season`, and a `MAX(cost_per_unit)` should be enough? — Luuk, Aug 17 '19 at 11:13
I used the MAX(cost_per_unit) and GROUP BY season approach, which worked. Also added UNIONs as I needed to compose a single query. I think that's the correct answer, as these are all things we have covered in my learning so far. Thanks! Now to post my next question, that gets my first up-vote. — MR. Ge, Aug 18 '19 at 02:31
I also moved the GROUP BY season into each query, following the FROM clause, and changed the WHERE season to HAVING season. — MR. Ge, Aug 18 '19 at 02:52
Having looked at other students work in review, I found the simple, 2 row solution using MAX() and GROUP BY, but not UNION, as described by Luuk. Far superior - I just have to understand the nuts and bolts of it... — MR. Ge, Sep 04 '19 at 23:42

DarkRob · Answer 1 · 2019-08-17T08:56:39.560

Although you can achieve same by using row_number(). By using partition by and row_number() you can group rows as per their category. And after that you can easily get the top 1 record from each group. For more info you may find this link for better understanding link.

select cost_per_unit, season from (
     select row_number() over (partition by season order by cost_per_unit) as Slno,
          * from fruit_imports where season != 'All Year'
 ) as tab where Slno = 1

If still want to follow your approach then, you apply order by at the last of your query which is ordering only last query section, because you took top 1 so it is not visible in your result.
If you want to apply order by on all then they must be grouped in one table object.

SELECT * FROM (
SELECT TOP 1 cost_per_unit, season
FROM fruit_imports
WHERE season != 'All Year'
AND season = 'Winter'

UNION 

SELECT TOP 1 cost_per_unit, season
FROM fruit_imports
WHERE season != 'All Year'
AND season = 'Spring'

UNION 

SELECT TOP 1 cost_per_unit, season
FROM fruit_imports
WHERE season != 'All Year'
AND season = 'Summer'

UNION

SELECT TOP 1 cost_per_unit, season
FROM fruit_imports
WHERE season != 'All Year'
AND season = 'Fall') as tab
ORDER BY cost_per_unit DESC

To get ORDER BY to work over several UNION-ed SELECT statements

1 Answers1