Last post Oct 17, 2015 03:55 AM by sandeepmittal11
Oct 15, 2015 08:04 AM|ManyTitles|LINK
Hi all, I have an app that lets users choose products based on four attributes namely; price, color, size, content, and age. Users can specify the importance of each attribute by giving each attribute a number from 1 to 5. The attribute with an importance
of 5 is the most important.
The user clicks the Submit button after choosing the values and importance of each attribute. These data are then sent to the Stored Proc that will return a list of product names that have all the attributes that the user chose, ordered by their importance.
My question is how do I dynamically change the order of items in the Order By clause based on the importance specified by the user. Thanks in advance for your help.
Oct 15, 2015 09:53 AM|limno|LINK
You can Use the sum of these importance weight in the order by clause like:
ORDER BY w1+w2=w3+w4+w5 DESC
Oct 15, 2015 12:09 PM|ManyTitles|LINK
Hi Limno, thanks for responding. Can you please explain how your suggested solution will order the attributes based on their associated importance value. Also when I say "ORDER" what I meant was if I want to display my attributes in descending order, I would
display the attribute with the highest importance value first followed by the attribute with the next highest importance value and so on.
Please see the example below.
Select * From Products
-- PriceI = 5, ContentI = 4, SizeI = 3, ColorI = 2, AgeI =1
Order By Price, Content, Size, Color, Age
I can probably use IF THEN Else in the Order By clause to do what I want but I was wondering if there is a more elegant solution.
Oct 15, 2015 01:52 PM|limno|LINK
You can translate your IF-ELSE logic into a CASE expression in T-sql to control the order you want.
Oct 16, 2015 04:15 AM|Krunal Parekh|LINK
Please see: http://www.sqlteam.com/article/dynamic-order-by
You could try it like this as described.
CREATE PROCEDURE ps_Customers_SELECT_DynamicOrderBy
@SortOrder tinyint = NULL
SELECT CompanyName, ContactName, ContactTitle
ORDER BY CASE WHEN @SortOrder = 1 THEN CompanyName
WHEN @SortOrder = 2 THEN ContactName
EXEC ps_Customers_SELECT_DynamicOrderBy 1
EXEC ps_Customers_SELECT_DynamicOrderBy 2
Oct 17, 2015 03:55 AM|sandeepmittal11|LINK
DECLARE @OrderBy VARCHAR(100)
SELECT @OrderBy = STUFF((
SELECT ',' + col
select 'Price' as col, @PriceI as val union all
select 'Content', @ContentI union all
select 'Size', @SizeI union all
select 'Color', @ColorI union all
select 'Age', @AgeI
) t ORDER BY val for xml path('')),1,1,' order by ')
declare @query nvarchar(500)
set @query = 'Select * From Products' + @OrderBy
exec sp_executesql @query