Keep ORDER BY after UNION

Question

I have a problem to solve. I have a table Occupationswith Name and Occupation.

My task is to: 1.Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).

query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:

There are a total of [occupation_count] [occupation]s.

If more than one Occupation has the same they should be ordered alphabetically.

I am almost done with the query

SELECT TEMP.CON1
FROM (
    SELECT NAME, CONCAT(NAME,'(', LEFT(OCCUPATION, 1),')') AS CON1
    FROM OCCUPATIONS
    ORDER BY NAME
    ) AS TEMP
UNION
SELECT TEMP2.CON2
FROM (
    SELECT COUNT(*) AS NR, CONCAT('THERE ARE A TOTAL OF ', COUNT(OCCUPATION),' ', OCCUPATION, 's') AS CON2
    FROM OCCUPATIONS
    GROUP BY OCCUPATION
    ORDER BY NR, OCCUPATION
) AS TEMP2

but I don't know how to keep the order of the first section after the two sections are united.

If anyone knows the answer I would be superglad for sharing.

Is there any need for using a `UNION`? Probably, it could be easier to solve this by two independent queries? — Nico Haase, Mar 07 '18 at 22:21
You have an extra subquery which you don't need in your query. To keep the order, you can put an order by in the query outside the union. — clinomaniac, Mar 07 '18 at 22:25
There are various ways to do it. Try checking this link https://stackoverflow.com/q/3531251/9453736 — Mishel Parkour, Mar 07 '18 at 22:26
You can't "keep" subquery order. Tables have no order. Query result sets [sic] have order. Anyway this is a faq, all you had to do was google your title. Please read [ask], other [help] links & the downvote arrow mouseover text. — philipxy, Mar 07 '18 at 22:26
@clinomaniac Yes order by appended to a union orders it, but that isn't "keeping" any subtable order, because there isn't any subtable order. — philipxy, Mar 07 '18 at 22:42

score 1 · Accepted Answer · answered Mar 08 '18 at 00:00

1

SELECT CON
FROM
(
SELECT 1 as SEQ, 0 as NR, NAME ,TEMP.CON1 as CON
FROM (
    SELECT NAME, CONCAT(NAME,'(', LEFT(OCCUPATION, 1),')') AS CON1
    FROM OCCUPATIONS
    ORDER BY NAME -- don't need 
    ) AS TEMP
UNION
SELECT 2, NR, null, TEMP2.CON2 
FROM (
    SELECT COUNT(*) AS NR, CONCAT('THERE ARE A TOTAL OF ', COUNT(OCCUPATION),' ', OCCUPATION, 's') AS CON2
    FROM OCCUPATIONS
    GROUP BY OCCUPATION
    ORDER BY NR, OCCUPATION -- don't need
) AS TEMP2 ) T 
ORDER BY SEQ, NR, NAME, CON

answered Mar 08 '18 at 00:00

kc2018

1,440
1
8
8

This is the best answer, do all your ordering at the end, not in the subqueries. – Andrew Mar 08 '18 at 00:02
@kc2018 Thanks a lot! – Artur Ferfecki Mar 08 '18 at 11:19
@Artek & kc2018 it's not "don't need", it's "has no effect". – philipxy Mar 09 '18 at 04:07

Andrew · Answer 2 · 2018-03-07T22:30:30.633

-1

UNION ALL would keep the order, and would work just as well in this case as your result sets are totally different.

The difference between union and union all is that UNION removes duplicates, which causes reordering, where UNION ALL simply adds the next set of results to the end of the existing result set. Since you are manipulating the strings on both selects, there is no real chance of there being duplicates in the results between TEMP and TEMP2. This will also get rid of some of the cost of the query, since UNION is more expensive than UNION ALL, as it needs to check for duplicates.

SELECT TEMP.CON1
FROM (
    SELECT NAME, CONCAT(NAME,'(', LEFT(OCCUPATION, 1),')') AS CON1
    FROM OCCUPATIONS
    ORDER BY NAME
    ) AS TEMP
UNION ALL
SELECT TEMP2.CON2
FROM (
    SELECT COUNT(*) AS NR, CONCAT('THERE ARE A TOTAL OF ', COUNT(OCCUPATION),' ', OCCUPATION, 's') AS CON2
    FROM OCCUPATIONS
    GROUP BY OCCUPATION
    ORDER BY NR, OCCUPATION
) AS TEMP2

This being said, the way I read the instructions, I wouldn't expect both results to be desired in the same result set.

edited Mar 07 '18 at 22:30

answered Mar 07 '18 at 22:28

Andrew

1,544
1
18
36

2

No, subqueries have no order. – philipxy Mar 07 '18 at 22:30
@philipxy Try it, UNION ALL will return results in the order the queries were added to the union, unless you apply additional ordering. There is an order inherent in all SQL, and as the lowest cost option the new rows are added to the end of the result set when unioned this way. – Andrew Mar 07 '18 at 22:33
1

No order is guaranteed. See my comment on the question and any SQL documentation. This is fundamental. – philipxy Mar 07 '18 at 22:34
1

https://stackoverflow.com/questions/15766359/does-union-all-guarantee-the-order-of-the-result-set – Aaron Dietz Mar 07 '18 at 22:34
@AaronDietz run that query in the question a million times, as the query is written, on any DBMS and you will always get ORDER. It may be hackish, it may not be intended, but the optimizer goes for the lowest cost, and the lowest cost IS appending the new results to the end of the result set. – Andrew Mar 07 '18 at 22:37
@Andrew . . . It is very strong of you to say "on any DBMS", especially because that is not true. The syntax is not even valid on some databases. – Gordon Linoff Mar 07 '18 at 23:12
@GordonLinoff Fair enough, on every DBMS I have used UNION ALL on, it has always without fail returned the values in the order each subquery returned them, and in the order they were unioned together. It is undocumented behavior, but it works. – Andrew Mar 07 '18 at 23:14

Keep ORDER BY after UNION

2 Answers2