[cfaussie] RE: Sort order from query return

[Steve Onnis]

Title: Message

try something like this

 

<cfset List = "3,1,2">
<cfquery>
 <cfloop from="1" to="#ListLen(List)#" index="i">
 SELECT myTable.*, '#i#' AS SortOrder
 FROM MyTable
 WHERE myTable_ID = #ListGetAt(List,i)#
 <cfif i LT ListLen(List)>
 UNION
 </cfif>
 </cfloop>
 Order By SortOrder  
</cfquery>

 

Regards

Steve Onnis
Domain Concept Designs
+61 422 337 685
+61 3 9431 4249

http://www.domainconceptdesigns.com [EMAIL PROTECTED]		http://www.cfcentral.com.au [EMAIL PROTECTED]

("If you think it can't be done, you haven't asked me!") - Steve Onnis

 

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Blake Foss
Sent: Friday, February 07, 2003 11:42 AM
To: CFAussie Mailing List
Subject: [cfaussie] Sort order from query return

Hi,

 

I am sure this is a simple one, but it has me stumped and I have too much to do.

 

I have a query that returns values based on a list,

 

<cfquery.....>

select *

from mytable

where mytable_ID IN ('1','2','3')

</cfquery>

 

How do I get the output to be displayed in the order that the where list went in? If I run the query it pulls the records out in the order they are entered in the database, but if I change the where list to ('2','3','1') is still returns in the 1 2 3 order.

 

Is this even possible within the query. I have a workaround using a tag that loops through each, but this is an awful solution and will not work within one section of the site.

 

Using CFMX on a Win2K server with SQL2000.

 

Thanks in advanced to any help.

 

Blake Foss

 

 

-----------------------------------------------------------------------------------

Blake Foss

Web Foot Forward

p: 61 2 9340 4401

f: 61 2 8080 8190

m: 0410 747 620

e: [EMAIL PROTECTED]

w: www.webfoot.com.au

 

---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]

MX Downunder AsiaPac DevCon - http://mxdu.com/

---
You are currently subscribed to cfaussie as: archive@mail-archive.com
To unsubscribe send a blank email to [EMAIL PROTECTED]

MX Downunder AsiaPac DevCon - http://mxdu.com/