SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Order by and Union
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jwells
Starting Member

17 Posts

Posted - 05/16/2013 :  13:30:48  Show Profile  Reply with Quote
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 - 05/16/2013 :  13:55:36  Show Profile  Reply with Quote
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 - 05/16/2013 :  14:00:28  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 05/17/2013 :  00:52:48  Show Profile  Reply with Quote
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 - 05/17/2013 :  10:17:27  Show Profile  Reply with Quote
Thanks for the response. Is this the correct way to do this or is there a better way?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000