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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.