SELECT DISTINCT and ORDER BY

Question

If I place DISTINCT keyword i get an error other wise it is working fine.

ERROR: Msg 145, Level 15, State 1, Procedure SP_Products_GetList, Line 15 ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

ALTER PROCEDURE [dbo].[SP_Products_GetList]    

@CatID int,
@CatName int,
@IsNew bit,
@InActive bit,
@SortBy varchar(50),
@SortType varchar(50)

AS    

SELECT DISTINCT Products.ProductID, ProductName, MAX(Price) Price, PriceID, [Description], Size, IsNew, InActive FROM (SELECT * FROM Products WHERE (@InActive is null or @InActive = InActive  ) AND ( @IsNew is null or @IsNew = IsNew )) Products
    INNER JOIN  ProductCategory
        on Products.ProductID = ProductCategory.ProductID 
    INNER JOIN  (
                    SELECT * FROM Categories 
                        WHERE 
                            ( @CatID is null or @CatID = CatID ) and
                            ( @CatName is null or @CatName = CatName )
                ) Categories 
        on ProductCategory.CatID = Categories.CatID 
    INNER JOIN ( 
                    SELECT Prices.ProductID, Prices.Price, Prices.PriceID, Prices.SizeID  FROM Prices 
                        INNER JOIN (
                            SELECT ProductID, max(Price) Price from Prices WHERE PriceID IN 
                                    ( SELECT MAX(PriceID) FROM Prices 
                                    GROUP BY ProductID , SizeID)
                            GROUP BY ProductID )  Prices_
                        ON Prices.ProductID = Prices_.ProductID AND Prices.Price = Prices_.Price                     
                ) as Prices 
        on Prices.ProductID = Products.ProductID 
        inner join  Sizes 
        on Sizes.SizeID = Prices.SizeID 
GROUP BY ProductName, CatName, Products.ProductID, Price, PriceID, [Description] ,Size, IsNew,InActive 
ORDER BY 
CASE @SortType 
    WHEN 'desc' THEN  
    CASE @SortBy         
        WHEN 'ProductName' THEN ProductName        
        END  
    END 
    DESC, 
CASE @SortType 
    WHEN 'desc' THEN  
    CASE @SortBy         
        WHEN 'ProductID' THEN Products.ProductID
        WHEN 'Price' THEN Price          
        END  
    END 
    DESC,     
CASE @SortType 
    WHEN 'asc' THEN  
    CASE @SortBy         
        WHEN 'ProductName' THEN ProductName        
        END  
    END 
    ASC, 
CASE @SortType 
    WHEN 'asc' THEN  
    CASE @SortBy         
        WHEN 'ProductID' THEN Products.ProductID
        WHEN 'Price' THEN Price          
        END  
    END 
    ASC

Answer 1

Please try with the following syntax

SELECT DISTINCT (Products.ProductID), ProductName, MAX(Price) Price,

Previous syntax is giving you error because of you have used column Products.ProductID many times in a procedure.

I have faced the same problem lot many times. So, above solutions had worked for me. Please try this with your procedure as i have not proper schema to test it.

Answer 2

What happens if you do...

SELECT ProductID, ProductName...
FROM (
       SELECT DISTINCT Products.ProductID, ProductName...
) AS OrderProduct
ORDER BY [your order code...]

Answer 3

So, a couple things here. I'm not saying I'm absolutely sure these are right but a few things to consider.

1. This message usually appears when you don't supply your order by column in the select distinct list. The only issue I could see here is that since [Price] is the alias of Max(Price) it is possibly confused? Try specifying Max(Price) in the dynamic order by clauses you are building at the bottom.

2. I noticed that your dynamic order by could potentially have a couple different types returned. You typically want to keep these order by clauses working with the same data type. However, it looks like you separated out the sort types (product id & price which I guess are numeric and productname which I would assume is a varchar).

So, nevermind 2, but give 1 a shot.