Union and Order By - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Union and Order By

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-11-08 : 09:24:01

Hi

I have data returned from a query that I wish to have seperate order by clause on some of the data. Is there a way of doing this? For instance I am trying to do this but it does not work and give error:

quote:
Incorrect syntax near the word union

select a, b, c, d, e, f
FROM table a
Where a.vehicleType IN (Car, Van)
ORDER BY a, b, c
UNION
select a, b, c, d, e, f
FROM table a
Where a.vehicleType IN (Truck, Motorbike)
ORDER BY d, e, f

Any ideas how I do this? From what I have read it appears you can only have one order by clause so wondering how to do order by on the different result sets?

Thanks

G

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-08 : 09:32:16

Since you are using UNION you can't talk about 'different result sets' because there is ONE result set returned.

No, you're never too old to Yak'n'Roll if you're too young to die.

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-08 : 09:34:13

What are you trying to accomplish by ordering the items in different groups like that?

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-11-08 : 10:05:41

quote:
Originally posted by TimSman

What are you trying to accomplish by ordering the items in different groups like that?

Some of the records for instance pertaining to vehicleType (truck, motocycle) require a different order by clause because of what is returned, whereas the other vehicle types require a slightly different order clause.

The data although all coming from the same columns is interepreted differently and means different things and so need to order appropriately to reflect that.

Hope that makes sense?

G

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-08 : 10:08:06

Can't the data be ordered in the front end?

Otherwise, you could dump the results for each search into temp tables, order there, and then combine them.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-11-08 : 10:09:00

quote:
Originally posted by webfred
Since you are using UNION you can't talk about 'different result sets' because there is ONE result set returned.

Is a union not the merging of two result sets into one with the same columns but different conditions, therefore I am trying to create different order bys on each set then obviously union into one result set? That is my question.

G

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-08 : 10:24:42

quote:
Originally posted by Grifter

quote:
Originally posted by webfred
Since you are using UNION you can't talk about 'different result sets' because there is ONE result set returned.

Is a union not the merging of two result sets into one with the same columns but different conditions, therefore I am trying to create different order bys on each set then obviously union into one result set? That is my question.

G

Then you need to take each result set, order it, THEN combine them with UNION.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-08 : 10:29:51

Consider that UNION is returning a DISTINCT result set, hence UNION is doing obviously more than just sticking the result sets together...

No, you're never too old to Yak'n'Roll if you're too young to die.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-11-08 : 10:57:50

I do not see much sense in what you are trying to do but maybe the following will help:


SELECT a, b, c, d, e, f
FROM [table]
WHERE vehicleType IN ('Car', 'Van', 'Truck', 'Motorbike') 
ORDER BY
	CASE WHEN vehicleType IN ('Car', 'Van') THEN a ELSE d END
	,CASE WHEN vehicleType IN ('Car', 'Van') THEN b ELSE e END
	,CASE WHEN vehicleType IN ('Car', 'Van') THEN c ELSE f END

Kristen
Test

22859 Posts

Posted - 2010-11-08 : 10:58:32

I have that scenario in my code - using UNION (or more probably UNION ALL) to get two recordsets into one so that they display in a single grid, but actually I want them in two separate "blocks" within that grid. Eaiser (for me) to Fudge the SQL rather than to have the front end have to bolt two resultsets together ...


SELECT *
FROM
(
select 1 AS BlockNo, a, b, c, d, e, f
FROM table a
Where a.vehicleType IN (Car, Van)
ORDER BY a, b, c
UNION -- Consider UNION ALL instead
select 2 AS BlockNo, a, b, c, d, e, f
FROM table a
Where a.vehicleType IN (Truck, Motorbike)
ORDER BY d, e, f
) AS X
ORDER BY CASE WHEN BlockNo = 1 THEN 1 ELSE 2 END,
         CASE WHEN BlockNo = 1 THEN a ELSE NULL END,
         CASE WHEN BlockNo = 1 THEN b ELSE NULL END,
         CASE WHEN BlockNo = 1 THEN c ELSE NULL END,
         CASE WHEN BlockNo = 2 THEN d ELSE NULL END,
         CASE WHEN BlockNo = 2 THEN e ELSE NULL END,
         CASE WHEN BlockNo = 2 THEN f ELSE NULL END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources