Doug,
Even though someone already posted a solution to your particular problem, I
thought I'd share a technique I've used in the past to do arbitrary sorting
of query results.
This method invloves using ColdFusion's Query of Queries functionality. I
will use your query as an example, and for the sake of this example, lets
assume you have the category ID of the "Other" category stored in a variable
called "otherCatID". In reality, you'ld probablly want to save this as some
kind of shared scope "pseudo" constant, but it doesn't really make any
difference. Anyways, here's the example code:
<!--- get results from the DB (unordered or not in the order you want) --->
<cfquery datasource="#request.site.dsn#" name="get_child_categories">
SELECT categories.cat_id,
categories.category,
categories.parent_id
FROM categories
WHERE categories.parent_id =
<cfqueryparam cfsqltype="cf_sql_integer" value="#cat_id#">
</cfquery>
<!--- use a query of queies to re-order the results how you want --->
<cfquery name="rsOrderedCategories" dbtype="query">
<!--- select all of the category records that are not "Other" --->
SELECT *, 0 as sortOrder
FROM get_child_categories
WHERE cat_id <> <cfqueryparam value="#otherCatID#"
cfsqltype="cf_sql_integer" />
UNION
<!--- select the "Other" category record --->
SELECT *, 1 as sortOrder
FROM get_child_categories
WHERE cat_id = <cfqueryparam value="#otherCatID#"
cfsqltype="cf_sql_integer" />
ORDER BY sortOrder, category
</cfquery>
Of course, you can do as many unions as you want. The idea is just to break
up the original result set into small pieces, where each piece is assigned a
"sortOrder", and then just using a union to lump them all back together
again.
In your case, the example using the CASE statement in the original SQL Order
By clause is a simpler solution. However, there are many cases where
ordering will greatly slow down the execution time of your query, and in
such cases, it may be beneficial to do the ordering using a query of
queries. It can also be a good way to re-order the same result set in a
modular way where you wouldn't need to have a bunch of different conditional
statements lumped in to the original query.
Hope this was helpful.
- Justin
On 9/27/06, Doug Brown <[EMAIL PROTECTED]> wrote:
>
> I have a list of categories, and I want to sort them ascending, but I need
> to have the category (other) always listed last. Anyone have ideas on how to
> accomplish this?
>
> IE:
> Baby Backpacks
> Baby Carriers and Slings
> Baby Jumpers
> Play Pens
> Vibrating Chairs
> Other
>
> My current query is like so...
>
> <cfquery datasource="#request.site.dsn#" name="get_child_categories">
> SELECT
> categories.cat_id,
> categories.category,
> categories.parent_id
> FROM
> categories
> WHERE
> categories.parent_id =
> <cfqueryparam cfsqltype="cf_sql_integer" value="#cat_id#">
> </cfquery>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:254693
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
