Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    7

    Unanswered: Recently Viewed Products

    Hi,

    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)
    42
    34
    57
    21

    SQL
    ---------------
    "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.

    Thanks for your time and any help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT * 
      FROM Products 
     WHERE Product IN ( 23,42,34,57,21 )
    ORDER
        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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2008
    Posts
    7
    Thank you very much, that works perfectly.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Personally, I'm a fan of leaving expressions out of the ORDER BY clause, so I would re-write Rudys suggestion as
    Code:
    SELECT *
    FROM   (
            SELECT *
                 , 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]
            FROM   dbo.products
            WHERE  product IN (23, 42, 34, 57, 21)
           ) As [a_subquery]
    ORDER
        BY order_by_this_expression
    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.
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your solution is obviously much better, because of the ELSE value

    as for "leaving expressions out of the ORDER BY," this is also a winning strategy, since standard SQL actually stipulates that ORDER BY can only reference columns, not expressions

    well done, young padawan
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    your solution is obviously much better, because of the ELSE value
    I don't think you appreciate how often that number appears in production
    Quote Originally Posted by r937
    as for "leaving expressions out of the ORDER BY," this is also a winning strategy, since standard SQL actually stipulates that ORDER BY can only reference columns, not expressions
    I picked up this method a while ago and stuck with it; and the more I used it, the more I forgot why - thanks for the refresh!
    Quote Originally Posted by r937
    well done, young padawan
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •