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?
gogetsomeAsked:
Who is Participating?
 
QuinnDexCommented:
select into temp using distinct, so you dont have duplicates in the temp table, you can then leave the distinct out of your select/orderby query as you know they are already distinct
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just in case it's not a typo, the above T-SQL is missing the final 'end'.
0
 
gogetsomeAuthor Commented:
just a copy paste error it is there in my original.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

0
 
gogetsomeAuthor Commented:
Sorry same error
0
 
gogetsomeAuthor Commented:
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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

0
 
gogetsomeAuthor Commented:
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.
0
 
gogetsomeAuthor Commented:
Yes that would be better. Thanks
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Oh kay...
0
 
QuinnDexCommented:
that wasnt the answer to your question, just an extension of other answers that were direct answers to your original question
0
 
QuinnDexCommented:
If anyone that offered a good solution for the poster wants to challenge this there will be no argument from me
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.