We help IT Professionals succeed at work.
Get Started

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

1,295 Views
Last Modified: 2014-05-08
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

Comment
Watch Question
Director of Information Technology
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 2 Answers and 3 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE