If this is SQL server should be able to accomplish it something like this...
CREATE PROCEDURE adhocorder
@position1 as varchar(25),
@position2 as varchar(25),
@position3 as varchar(25),
@position4 as varchar(25)
AS
SELECT AdHocOrder = CASE bp.Description
WHEN @position1 THEN 1
WHEN @position2 THEN 2
WHEN @position3 THEN 3
WHEN @position4 THEN 4
ELSE 5
END,
bp.description,
m.membername
FROM members m,
boardposition bp,
board b
WHERE m.memberid = b.memberid
AND bp.boardpositionid = b.boardpositionid
ORDER BY adhocorder
Somthing like this should allow yuo to pass in varchars for positions 1-4
indicating which should appear where.
Justin
-----Original Message-----
From: Jim McAtee [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 09, 2001 5:06 PM
To: CF-Talk
Subject: Re: Imposing an ad hoc sort order on a SQL query
Thanks, but that's what I was trying avoid - storing the sort order within
the table. Even though it's flexible enough to be updated, I might have
another page and query (perhaps pulling just a subset of the board members)
where I want to use a different ordering.
But now that I think about it, doing this so-called ad hoc sort in the way I
was proposing may not be the smartest idea, since it's not data driven. It
would require me to hardcode the board position descriptions into the
template's SQL code. I guess I'll do it the way you've described and then
add additional sort order fields as needed by the project.
Jim
----- Original Message -----
From: "Paris Lundis" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, July 09, 2001 2:27 PM
Subject: Re: Imposing an ad hoc sort order on a SQL query
> 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
