I have had to do this in the past... I just added a USERSORT field and
put numeric fields in it for display order (ie: PRESIDENT= 1
VICEPRESIDENT=2, etc.)
then I just create a forms based interface that lets you mod the
numbers as need be...
so you do a SORT BY USERSORT ASC in my example...
-paris
[finding the future in the past, passing the future in the present]
[connecting people, places and things]
-----Original Message-----
From: "Jim McAtee" <[EMAIL PROTECTED]>
Date: Mon, 09 Jul 2001 11:39:25 -0600
Subject: Imposing an ad hoc sort order on a SQL query
> Say I've got three tables for the members of an organization and its
> Board
> of Directors. Let's say there are approximately ten board positions.
> I
> want to be able to do a query and have an "ad hoc" ordering of the
> positions
> for display on a page. For example:
>
> 1. President - John Doe
> 2. Vice President - Jane Jones
> 3. Secretary - Bob Smith
> 4. Treasurer - Neil Jones
> etc.
>
> Tables:
>
> Members
> --------------------
> MemberID
> Name
>
> BoardPositions
> ---------------------
> BoardPositionID
> Description
>
> Board
> ---------------------
> BoardID
> MemberID
> BoardPositionID
>
>
> I could add a 'sortorder' field to the Board table, but I don't think
> this
> should be in the data. Rather, I'd like to be able to specify the
> order
> when I do the query in my CF template. Is there any means in SQL to
> do
> something like:
>
> SELECT Members.Name, BoardPositions.Description
> FROM Board b, BoardPositions p, Members m
> WHERE b.MemberID = m.MemberID
> AND b.BoardPositionsID = p.BoardPositionsID
> ORDER BY ('President','Vice President', ...)
>
>
> Thanks,
> Jim
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
