gogetsome
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.
How to repair my statement?
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
How to repair my statement?
Just in case it's not a typo, the above T-SQL is missing the final 'end'.
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...
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
ASKER
Sorry same error
ASKER
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
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.
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
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes that would be better. Thanks
Oh kay...
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.