Query ,Union clause and Order by 3

[drmichaelw]

I have individual, identical select statements that vary only on one value in the where clause. Each select statement has an order by clause. I would like to create an UNION since the select parts are all the same. The problem is that I can't use the order by clause for each seperate select in the UNION. It only allows the first select to use the ORDER by. Any solutions

 

[r937]

i can understand how you concluded that "it only allows the first select to use the ORDER BY" but that's not exactly the problem

the entire query, called a fullselect and consisting of a bunch of subselects all UNIONed together, can have only one ORDER BY

you got a syntax error on the UNION keyword because once you code ORDER BY for the first subselect, you are basically saying there's only one subselect in the query

so try this --

[tt]select foo from bar
union
select quz from baz
order by 1[/tt]

the "1" in the ORDER BY refers to the 1st column, and is valid syntax in most databases that i can think of

otherwise, use the column name(s) from the first subselect

rudy

http://rudy.ca/

 

[rac2]

There can be only one ORDER BY clause and it must be the last clause in the query. It operates on the result of the UNION so that rows from either subset are placed in order relative to the whole set.

If you need the rows to be ordered within the subsets instead of over the whole set then add a constant to each query that identifies the subset and include the constant in the ORDER BY.


Like so -

SELECT "M" AS Gender, agemos, P50 FROM tblGrowth WHERE sex="1"
UNION
SELECT "F" AS Gender, agemos, P50 FROM tblGrowth WHERE sex="2"
ORDER BY Gender, P50 DESC
;

 

[r937]

rac2, the star is for the added suggestion to use a literal to distinguish rows from each subselect -- i do that all the time too

but then it should be UNION ALL instead of UNION, eh

no duplicates between subselects if their rows are distinguished

 

[drmichaelw]

Rac2 or r937 can you clarify the use of a constant.
Is P50 a field name with a value associated with it?
I'm not sure how the constant or a literal is helping. Can you explain it again?

Thank you,

 

[r937]

P50 in rac2's example has to be a column name

the literal used to distinguish rows is importqant if you need to know which of the subselects a row came from

assume there are different tables for supervisors and employees --

select fname, lname, 's'
from supervisors
union all
select firstname, lastname, 'e'
from employees

without the 's' or 'e' you wouldn't know which of the tables the people came from


rudy

 

[rac2]

Hello Rudy,

Thanks for the star. That's what it's all about.

You're quite right about UNION ALL if there are identical rows within a subquery. UNION eliminates duplicates, UNION ALL keeps them. But then if we use constants or a column value to mark the queries then none of the rows in the first query would match a row in the second query.

Even so the burden is on drmichaelw to decide whether he wants duplicate rows within subqueries in his result.

Regards,
Richard

 

[drmichaelw]

Thanks everyone,

You cleared this issue up for me.
Star for all