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
> >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
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
