#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    10
    Rep Power
    0

    Keep the list order when using IN (<cfqueryparam list="xyz" ...)


    I am passing a list of 25 SKU's to a cfquery and need to return the data pulled in the same order that the list is in. When I pass the list to the query in the where statement (see below) the order the items come out of the query are not in the same order:

    "where products.pid in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#skusfound#" list="yes">)"

    The only way I can get this to work is to do a loop around the list and query each item one-by-one. Unfortunately, this method is much slower than using a <cfoutput query="">.

    Is there any way to get the first query to output the items in the same order as the list - or is the loop the only way to do this?

    Thank you in advance.

    Ron
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    If there's no "logic" to how the SKUs should be ordered other than the order in the list, the only other option is to generate a custom order by clause. Loop over the SKUs and create an order by that looks like:

    Code:
    ORDER BY 
        CASE WHEN products.pid = 'First SKU' THEN 1
            WHEN products.pid = 'Second SKU' THEN 2
            WHEN products.pid = 'Third SKU' THEN 3
            ...etc...
        END

IMN logo majestic logo threadwatch logo seochat tools logo