Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2009-02-21
Medium Priority
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

Jones911 earned 180 total points
ID: 23701623
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


Accepted Solution

eszaq earned 195 total points
ID: 23702523
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

ID: 31549640
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: http://www.experts-exchange.com/Web_Development/Software/ColdFusion_Studio/Q_24166740.html

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

564 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