I am hoping someone can tell me how I fix this problem. I have looked high and low on the web for a solution.
I am storing the last 5 products that have been viewed by a customer in a cookie using classic ASP.
When I create a SELECT statement in order to display these as links on the page, the recordset that is returned is not in the order that I plug into it (i.e. the order in which products have been viewed), which is obviously more useful and what I would like to achieve.
COOKIE CONTAINS FOLLOWING DATA
23 (the most recent)
"SELECT * FROM Products WHERE Product = 23 OR Product = 42 OR Product = 34 OR Product = 57 OR Product = 21"
Is there any way I can get a recordset that matches the the order in which I create the SELECT statement? I have thought about arrays and such but want as simple a script as I can get without lots of database accesses.
WHERE Product IN ( 23,42,34,57,21 )
BY CASE WHEN Product = 23 THEN 1
WHEN Product = 42 THEN 2
WHEN Product = 34 THEN 3
WHEN Product = 57 THEN 4
WHEN Product = 21 THEN 5 ELSE 999 END
Personally, I'm a fan of leaving expressions out of the ORDER BY clause, so I would re-write Rudys suggestion as
, CASE product
WHEN 23 THEN 1
WHEN 42 THEN 2
WHEN 34 THEN 3
WHEN 57 THEN 4
WHEN 21 THEN 5
ELSE 937 END As [order_by_this_expression]
WHERE product IN (23, 42, 34, 57, 21)
) As [a_subquery]
Obviously, if you don't want to return the order_by_this_expression column, you just change the SELECT * (which you should avoid anyway) to a comma, separated, list, of, columns.
P.S. used a different CASE construct that will be marginally more efficient (as product will only be evealuated once for each row)... in theory.