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

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.
slick_moeAsked:
Who is Participating?
 
eszaqCommented:
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
</cfquery>



0
 
Jones911Commented:
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#" /> )
</cfquery>

Open in new window

0
 
slick_moeAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.