"ORDER BY" not working when used with UNION

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

SQL Server 2005 Forums

Transact-SQL (2005)

"ORDER BY" not working when used with UNION

Author

Topic

darenkov
Yak Posting Veteran

90 Posts

Posted - 2007-10-07 : 04:55:14

i don't understand this error:

this is my code but i the following error:

Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "S.Rank" could not be bound.
Msg 104, Level 16, State 1, Line 10
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

---------------------------- MY CODE BELOW

DECLARE @CategoryID int
DECLARE @ProductID int --

SET @ProductID = 4 --

SET @CategoryID = (SELECT CategoryID
FROM Products
WHERE ProductID = @ProductID)

SELECT 0 AS 'SizeID', '--> Select Size' AS 'SizeDescription' UNION
SELECT S.SizeID,
S.SizeDescription
FROM Sizes S
INNER JOIN
CategorySizes CS
ON S.SizeID = CS.SizeID
LEFT OUTER JOIN
Quantities Q
ON Q.SizeID = S.SizeID
WHERE CS.CategoryID = @CategoryID
AND Q.SizeID is NULL
ORDER BY S.[Rank]

darenkov
Yak Posting Veteran

90 Posts

Posted - 2007-10-07 : 08:42:57

ok sussed it out.

just wrapped the query and gave it an alias then ordered the rank based on the column through the alias

Kristen
Test

22859 Posts

Posted - 2007-10-07 : 13:10:23

Yup, that's most probably the easiest way to do it. Bit of a Pain though ... Ho!Hum!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources