specify ordering for query's recordset

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tid0rz - 30 Nov 2004 02:22 GMT

Im having trouble customizing the order of output from a query. sql cannot do
what i need because it orders numerically or alphabetically. I need it to sort
based on fixed input.
For example. If I have records that contains a string and an id, and if the
example db resembed...

ID STRING
987 brown
456 how
135 cow
753 now

and normal output if sorted by id or by string will result incorrectly...
sort by id yields [ cow how now brown ]
sort by string yields [ brown cow how now ]

and of course the correct ordering is [ how now brown cow ]

I need a way to specify the exact sort I want, by saying that I want yyy field
(in this case ID) to order the same as a hardcoded list (ie sort_list =
"456,753,987,135" )

any ideas?

thanks,
-john

**note, Ive been messing around with <cfloop list="...,..."> with no luck.

Reply to this Message

blewis - 30 Nov 2004 05:44 GMT

This answer may be overkill, but I cannot think of another easy way without
storing the order in the DB record itself. I put everything from the DB in a
Struct so it can be referenced by ID as a key.

Assuming your query is called "qBrownCow", try:

<cfset sort_list = "456,753,987,135">

<cfset stQuery = StructNew()>
<cfloop query="qBrownCow">
<cfset y = StructInsert(stQuery, qBrownCow.ID, qBrownCow.STRING)>
</cfloop>

<cfoutput>
    <cfloop index="i" list="#sort_list#">
        #stQuery[i]#
    </cfloop>
</cfoutput>

Reply to this Message

Stefan K. - 30 Nov 2004 10:49 GMT

MS SQL Server example:

<cfquery name="qsMyQuery" datasource="" dbtype="ODBC">
    SELECT        id, string
    FROM            myTable
    ORDER BY    CASE id
                            <cfset myList = "456,753,987,135">
                            <cfloop index="i" from="1" to="#ListLen(myList)#">
                                WHEN #ListGetAt(myList, i)# THEN #i#
                            </cfloop>
                            ELSE 0
                        END ASC
</cfquery>

Reply to this Message

Webmaster Forum / ColdFusion / Advanced Techniques / November 2004

specify ordering for query's recordset