Link to home
Start Free TrialLog in
Avatar of gogetsome
gogetsomeFlag for United States of America

asked on

Need help with order by and distinct

Hello, I get the following error when running my select statement but all the order by columns are in the select...

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

Select Distinct Top 20 (productid) as ProductId, title, ITEM_RANK, price  from #Temp
		
		Order by case when @SortBy = 'Title' then Title  when @sortby = '' then Title end
		, case when @SortBy = 'Price' then Price  when @sortby = '' then Price end
		, case when @SortBy = 'BestSeller' then ITEM_RANK  when @sortby = '' then ITEM_RANK

Open in new window


How to repair my statement?
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Just in case it's not a typo, the above T-SQL is missing the final 'end'.
Avatar of gogetsome

ASKER

just a copy paste error it is there in my original.
Is there a difference between @Sortby and @sortby?  Last I remember variables are case insensitive, so that it would not be possible to declare both.

Try this...
Select Distinct Top 20 (productid) as ProductId, title, ITEM_RANK, price 
from #Temp
Order by case when @SortBy = 'Title' then title when @SortBy = 'Price' then Price when @SortBy = 'BestSeller' then ITEM_RANK ELSE something_else END

Open in new window

Sorry same error
This worked but I do not know why. Anyone?

Select  Top 20 (productid), title, ITEM_RANK, Price from #Temp
where Exists(
select Distinct(productId), Title, ITEM_RANK, Price      From #Temp
)
Order by case when @SortBy = 'Title' then Title  when @sortby = '' then Title end
, case when @SortBy = 'Price' then Price  when @sortby = '' then Price end
, case when @SortBy = 'BestSeller' then ITEM_RANK  when @sortby = '' then ITEM_RANK end
Hmm ... not sure you'd need DISTINCT with a TOP x, seems redundant.

The only other thing that comes to mind is that TOP and ORDER BY CASE ... don't play well together.
Just for kicks and giggles, run this and see if it works..
Declare @Sortby varchar(10) = 'Title'

IF @Sortby = 'Title'
	Select Top 20 productid as ProductId, title, ITEM_RANK, price 
	from #Temp
	Order by title
ELSE IF @Sortby = 'Price'
	Select Top 20 productid as ProductId, title, ITEM_RANK, price 
	from #Temp
	Order by price
ELSE IF @Sortby = 'BestSeller'
	Select Top 20 productid as ProductId, title, ITEM_RANK, price 
	from #Temp
	Order by ITEM_RANK 
ELSE
	-- Default sort order.  Make something up here. 
	Select Top 20 productid as ProductId, title, ITEM_RANK, price 
	from #Temp
	Order by productid

Open in new window

Jim that works but I get duplicates. The #Temp contains duplicate products. I fill the table using different search and fulltext searches. Probably not the quickest solution.
ASKER CERTIFIED SOLUTION
Avatar of QuinnDex
QuinnDex

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes that would be better. Thanks
Oh kay...
Avatar of QuinnDex
QuinnDex

that wasnt the answer to your question, just an extension of other answers that were direct answers to your original question
If anyone that offered a good solution for the poster wants to challenge this there will be no argument from me
Yeah I have no objections to the comment itself, as I didn't think to change the way #temp was populated as the basis of answering the question.  I'm just curious as to why the asker did not find any of my comments helpful.