That's good to know, however, as far as I an tell, that uses a simple
numeric value (aggregate values) to sort on, whereas you are sorting
arbitrarily.
If you don't have access to the table itself, you can use something
like QueryAddColumn to insert your arbitrary sort values into the
query.
QueryAddColumn(query, column-name, array-name)
This might be the way to go, if you don't have access to the DB.
Good luck,
Jamie
On Thu, 6 Dec 2001 10:01:32 -0800, in cf-talk you wrote:
>I see this in the SQL help files, but not sure how to write out the
query
>for it....
>
>Order
>Arranges members of a specified set, optionally preserving or breaking
th
>e
>hierarchy.
>
>Syntax
>Order(«Set», {«String Expression» | «Numeric Expression»}
>[, ASC | DESC | BASC | BDESC])
>
>Remarks
>There are two varieties of Order: hierarchized (ASC or DESC) and
>nonhierarchized (BASC or BDESC, where B stands for Break hierarchy). The
>hierarchized ordering first arranges members according to their position
>in
>the hierarchy. Then it orders each level. The nonhierarchized ordering
>arranges members in the set without regard to the hierarchy. In the
absen
>ce
>of an explicit specification, ASC is the default.
>
>Example
>This example
>
>Order(SampleSet, ([1995], Sales), DESC)
>
>hierarchizes all members and sorts each level according to Sales. Sales
a
>re
>compared at the highest level when the sorted list is constructed.
>Therefore, if the sum of Sales in all California cities is less than the
>sum
>of Sales in all New York cities, California and California.LA will
appear
>below NYC in the sorted, descending list.
>
>The result of
>
>Order(SampleSet, ([1995], Sales), DESC)
>
>is listed in the following table.
>
> Location 1995 sales
> USA 5000
> California 2000
> LA 500
> Buffalo 300
> NYC 900
> France 2500
> Paris 365
> Nice 27
> UK 1900
> London 250
>
>
>The following expression sorts the members according to their values
with
>out
>regard for their relative positions in the member hierarchy. In this
>example, numeric values are sorted by 1995 sales per city, including
>aggregate sales values by state and country:
>
>Order(SampleSet, ([1995], Sales), BDESC)
>
>The following table shows the result of the previous expression.
>
> Location 1995 sales
> USA 5000
> France 2500
> California 2000
> UK 1900
> NYC 900
> LA 500
> Paris 365
> Buffalo 300
> London 250
> Nice 27
>
>
>
>
>Note When the input set has two elements for which the «String
Express
>ion»
>or «Numeric Expression» has the same value, the input order is
preser
>ved.
>
>
>For example, if the sales for USA and Europe is 300 each, and the sales
f
>or
>Asia is 100, the following expression returns the set {Asia, USA,
Europe}
>,
>not the set {Asia, Europe, USA}:
>
>Order({USA, Europe, Asia}, Sales, BASC)
>
>©1988-2000 Microsoft Corporation. All Rights Reserved.
>
>
>Doug
>
>
>
>----- Original Message -----
>From: "Jamie Jackson" <[EMAIL PROTECTED]>
>To: "CF-Talk" <[EMAIL PROTECTED]>
>Sent: Thursday, December 06, 2001 9:42 AM
>Subject: Re: SQL?
>
>
>> In tables where I need arbitrary sorting ability, I've set up a
>> numeric "sortby" field, wherein I order items manually (could get
>> tedious if you have 100 rows in a table, but I never have that many in
>> an arbitrarily-sorted table). I set the default value to, say, 100 or
>> 1000, so that items are automatically low-sort priority by default. I
>> value my first priority item 10, my second item 20, and so on. Then I
>> ORDER BY that field in my SQL.
>>
>> You could make a simple javascript/CF sorting app to take care of your
>> sorting, if you grow tired of juggling numbers.
>>
>> If others have a better way, I'd like to know.
>>
>> Thanks,
>> Jamie
>>
>>
>>
>> On Thu, 6 Dec 2001 09:32:40 -0800, in cf-talk you wrote:
>>
>> >I have a page where I am dynamically creating 20 dropdowns from a
quer
>y,
>>
>> >and what I need to be able to do is order it exactly how I want. I
>> >cannot use asc or desc. Is this possible in SQL? Maybe ordering it by
>> >column names or something?
>> >
>> >
>> >
>> >DB
>> >
>> >
>>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
