Geek Answers Handbook

SELECT DISTINCT and ORDER BY

If I put the DISTINCT keyword, I get an error, but it works fine.

ERROR: Msg 145, level 15, state 1, procedure SP_Products_GetList, line 15 ORDER BY elements should be displayed in the selection list if the SELECT DISTINCT parameter 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
+4
source share
5 answers

What happens if you do ...

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

So a couple of things here. I am not saying that I am absolutely sure that this is correct, but a few things to consider.

  • This message usually appears when you do not submit your order by column in the selected list. The only problem I could see here is that since [Price] is an alias of Max (Price), is it possibly confusing? Try to specify Max (Price) in a dynamic order for the articles that you build below.

  • I noticed that your dynamic order can have a couple of different types. Usually you want to keep this order using sentences that work with the same data type. However, it looks like you have highlighted the sort types (product identifier and price, which I assume are numeric, and the product name I would suggest is varchar).

So, nevermind 2, but take 1 shot.

+3
source

I ran into the same problem when trying to arrange by calculations on my table (I tried to arrange by distance records based on latitude and longitude columns).

I would get an error, although I included the latitude and longitude columns in my selection.

I found this to work if I included the same calculation in my choice:

 SELECT DISTINCT ProductID, ProductName, ({my calculation}) AS distance FROM my_table ORDER BY {my calculation} ASC
+1
source

Try using the following syntax

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

The previous syntax gives you an error due to reusing the Products.ProductID column in the procedure.

I have come across the same problem many times. So the solutions worked for me. Please try this with your procedure, as I do not have the correct scheme for checking it.

0
source

The solution for working around DISTINCT will be as follows:

 SELECT productName, productPrice FROM Product GROUP BY productName, productPrice ORDER BY productName
0
source

More articles:


All Articles