Coldfusion - how to find lowest and highest number in comma-delimited list

Posted on 2009-02-21
Last Modified: 2013-12-24
If I have a comma-delimited list of numbers (1,3,6,11,12) how can I use cfquery to search a field for each of these six numbers?  For example, I have an ID field where I want all records returned that have an value equal to each number in the list.
Question by:slick_moe
    LVL 19

    Assisted Solution

    Use the list attribute on cfqueryparam.
    <cfset someList = "1,3,6,11,12" />
    <cfquery name="q" datasource="myDSN">
    	select		*
            from            tableName
            where		id in ( <cfqueryparam list="true" cfsqltype="cf_sql_integer" value="#someList#" /> )

    Open in new window

    LVL 8

    Accepted Solution

    title of the question is a little bit confusing. You want lowest and highest from the list... Not clear before running dadabase query or after.

    Anyway, if before query, consider using function ListSort(list, sort_type [, sort_order] [, delimiters ]). E.g.:
    <cfset someList = "3,1,6,12,11" />
    <cfset sortedList = ListSort(someList, "numeric", "asc", ",")/>
    <cfset lowest = listFirst(sortedList,",")>
    <cfset highest = listLast(sortedList,",")>

    Or you can select record with highest or lowest ID in the query using top 1 and order by (asc or desc):
    <cfquery name="q" datasource="myDSN">
            select          top 1 *
            from            tableName
            where           id in ( <cfqueryparam list="true" cfsqltype="cf_sql_integer" value="#someList#" /> )
            order by        id  desc


    Author Closing Comment

    Thanks for the help, your solutions were right on.  In the process I realized I was after something a bit different so I awarded your points and opened up a new question... I hope you can help further:

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
    The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now