Avatar of Southern_Gentleman
Southern_Gentleman
Flag for United States of America asked on

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

I have a Select Distinct query with a Order By but I keep getting this error when I debug in Visual Studio ASP.net. It runs fine when I use the Query Builder in VS 2012 but when I run it comes up with 'ORDER BY items must appear in the select list if SELECT DISTINCT is specified'.
SELECT DISTINCT 
                      Orders.OrderID, Orders.BillingCompanyName, Orders.ShipCity, Orders.ShipState, CONVERT(Datetime, Orders.Custom_Field_InHandsDate, 101) AS InHands, 
                      Orders.Locked, DATEDIFF(Day, Orders.orderdate, CONVERT(date, Orders.Custom_Field_InHandsDate)) AS LeadTime, 
                      CASE WHEN Orders.Total_Payment_Received = 0 THEN 0 ELSE Orders.Total_Payment_Received / Orders.PaymentAmount END AS Deposit, 
                      CAST(CASE WHEN (FactoryPO2.POName IS NULL OR
                      FactoryPO2.FactoryName IS NULL OR
                      FactoryPO2.POName IS NULL) THEN 'false' ELSE 'true' END AS bit) AS POcreated, CAST(CASE WHEN (FactoryPO1.Color IS NULL OR
                      FactoryPO1.Color IS NULL OR
                      FactoryPO1.Pantone IS NULL OR
                      FactoryPO1.ColorCat IS NULL OR
                      FactoryPO1.ColorCategory IS NULL OR
                      FactoryPO1.Style IS NULL OR
                      FactoryPO1.ModelName IS NULL OR
                      FactoryPO1.FabricName IS NULL OR
                      FactoryPO1.FabricType IS NULL) THEN 'false' ELSE 'true' END AS bit) AS OrderEntry, CAST(CASE WHEN (FactoryPO5.XFactoryDate IS NULL OR
                      FactoryPO5.ShipDate IS NULL OR
                      FactoryPO5.CNForward IS NULL OR
                      FactoryPO5.ArrivalPort IS NULL OR
                      FactoryPO5.ETA IS NULL) THEN 'false' ELSE 'true' END AS bit) AS ShipDates, CAST(CASE WHEN (Image.Image1 IS NULL) THEN 'false' ELSE 'true' END AS bit) 
                      AS Mockup
FROM         Orders INNER JOIN
                      OrderDetails ON Orders.OrderID = OrderDetails.OrderID LEFT OUTER JOIN
                      Image ON Orders.OrderID > Image.OrderID LEFT OUTER JOIN
                      FactoryPO5 ON Orders.OrderID > FactoryPO5.OrderID LEFT OUTER JOIN
                      FactoryPO1 ON Orders.OrderID > FactoryPO1.OrderID LEFT OUTER JOIN
                      FactoryPO2 ON Orders.OrderID > FactoryPO2.OrderID
WHERE     (OrderDetails.ProductCode LIKE N'C-%') AND (Orders.Custom_Field_AccountManager = @Custom_Field_AccountManager) AND 
                      (NOT (Orders.OrderStatus LIKE N'Shipped')) AND (NOT (Orders.OrderStatus LIKE N'Cancelled')) AND (NOT (Orders.OrderStatus LIKE N'Returned'))
ORDER BY InHands

Open in new window

Microsoft SQL Server 2008ASP.NET

Avatar of undefined
Last Comment
Southern_Gentleman

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Kyle Abrahams

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Southern_Gentleman

ASKER
It took a little bit of adjusting since it kept auto reverting to the alias. When I tried the Cross Apply Visual Studio said it does not support it.  I could have used a stored procedure but didn't want to go that route with this query. Thanks again.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy