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.
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 sorderYields:Qty Item Description Each Total sorder1 Freight $200.00 200 21 PerDiem HOTEL $24.00 24 22 311-204 Carbonator, Tank $283.71 567.42 12 525-100 Collar, High Yie $0.44 0.88 13 525-100 Collar, High Yield $0.55 1.65 14 013-002 Pump, Air Motor Assy $359.33 1437.32 14 519-100 Kit, Hose CO2 Fill/Ven $318.24 1272.96 112 511-404 Elbow, 1/4'' Barb W/Ch $7.30 87.6276 1200 511-404 Elbow, 1/4'' Barb W/Ch $7.30 1460.46 1 |
|
|
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? |
|
|
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 fieldIn case what you're looking at are getting values side by side then you should be using joins instead rather than union------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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? |
|
|
|
|
|
|
|