UNION puzzle

[esdee]

i have
select a, b, c from t1 -- q1
union
select w, f, v from t2 --q2

in the result set q1 come below result set of q2
(w, f, v
q, b, c)

how come ?

 

[Zakhar]

use union all
union implicitly sorts the output

 

[donutman]

The "published" difference between UNION and UNION ALL is that the later will include duplicate data if the same row of data exists in both queries.
You cannot include ORDER BY clauses in the union sets.
-Karl

 

[checkai]

I beg to differ as far as using ORDER BY...I believe you can...I use it to generate lists where the first item is a blank...

ex:

SELECT	C.CLIENTGROUPID AS GROUPID,
	C.CLIENTGROUP AS 'GROUP'
FROM	TBLCLIENTGROUP C
UNION
SELECT	0 AS GROUPID,
	'<< Select >>' AS 'GROUP'
ORDER
BY	'GROUP' asc

 

[Pattycake245]

I agree with chekai. I do order by's in some of my stored procs that have unions. You can only do an order by after the last union, not after each one.

Tim

 

[donutman]

I love this forum. You learn new things and you relearn old things the right way.
-Karl

 

[esdee]

ahem..well, yeah
i know about union all and order by with unions, but
can anybody answer MY question?
of what i've read by now it seems that union automatically orders the rows, is that correct ?

 

[swampBoogie]

If you don't have an order by the result can be in any order.

 

[jkelly2956]

Why not tag a static field onto the end of each q1 and q2 and order by that.

SELECT    C.CLIENTGROUPID AS GROUPID,
    C.CLIENTGROUP AS 'GROUP','0' as gOrder
FROM    TBLCLIENTGROUP C
UNION
SELECT    0 AS GROUPID,
    '<< Select >>' AS 'GROUP', '1' as gOrder
ORDER
BY    gOrder,'GROUP' asc

 

[esdee]

thats an interesting idea, jkelly, thanx