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
 Order by and Union

Author  Topic 

jwells
Starting Member

17 Posts

Posted - 2013-05-16 : 13:30:48
I have a select statement that has two queries that uses union. The query works fine except I would like the results returned to be all from the first query then all from the second query on the other side of the union. How do I do that?

jwells
Starting Member

17 Posts

Posted - 2013-05-16 : 13:55:36
This is what I've tried by assigning a value to sorder from each query and then tried ordering with that. Doesn't order properly:

SELECT TOP 100 PERCENT *
FROM (SELECTdbo.WOOtherCharges.Quantity AS Qty, dbo.MiscCharges.ChargeNum AS Item, dbo.WOOtherCharges.Description,
dbo.WOOtherCharges.Price AS Each, dbo.WOOtherCharges.Quantity * dbo.WOOtherCharges.Price AS Total, 2 AS sorder
FROM dbo.WOTrips INNER JOIN
dbo.WOOtherCharges ON dbo.WOTrips.ID = dbo.WOOtherCharges.WOTripID INNER JOIN
dbo.MiscCharges ON dbo.WOOtherCharges.ItemNum = dbo.MiscCharges.ID
WHERE dbo.WOTrips.WOID = 21470
UNION
SELECT dbo.WOParts.Quantity AS Qty, dbo.Parts.PartID AS Item, dbo.Parts.Description, dbo.WOParts.Price AS Each,
dbo.WOParts.Quantity * dbo.WOParts.Price AS Total, 1 AS sorder
FROM dbo.WOParts INNER JOIN
dbo.WOTrips ON dbo.WOParts.WOTripID = dbo.WOTrips.ID INNER JOIN
dbo.Parts ON dbo.WOParts.PartID = dbo.Parts.ID
WHERE dbo.WOTrips.WOID = 21470) DERIVEDTBL
ORDER BY sorder

Yields:
Qty Item Description Each Total sorder
1 Freight $200.00 200 2
1 PerDiem HOTEL $24.00 24 2
2 311-204 Carbonator, Tank $283.71 567.42 1
2 525-100 Collar, High Yie $0.44 0.88 1
3 525-100 Collar, High Yield $0.55 1.65 1
4 013-002 Pump, Air Motor Assy $359.33 1437.32 1
4 519-100 Kit, Hose CO2 Fill/Ven $318.24 1272.96 1
12 511-404 Elbow, 1/4'' Barb W/Ch $7.30 87.6276 1
200 511-404 Elbow, 1/4'' Barb W/Ch $7.30 1460.46 1
Go to Top of Page

jwells
Starting Member

17 Posts

Posted - 2013-05-16 : 14:00:28
Correction: It does not work in my query editor but when I plug it into VB it works fine. Is this the way it should be done?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 00:52:48
the way you've it now it will fetch you results from second query first followed by first query results. If you want opposite way swap the values for Sorder field

In case what you're looking at are getting values side by side then you should be using joins instead rather than union

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jwells
Starting Member

17 Posts

Posted - 2013-05-17 : 10:17:27
Thanks for the response. Is this the correct way to do this or is there a better way?
Go to Top of Page
   

- Advertisement -