RE: Here's an interesting little problem...
Guess you can do that too ;-) I like it better in some ways than the case (it's easier to create a dynamic list), though the case statement would be easier to read... /*-Original Message- /*From: Greg Morphis [mailto:[EMAIL PROTECTED] /*Sent: Tuesday, August 26, 2008 2:42 PM /*To: CF-Talk /*Subject: Re: Here's an interesting little problem... /* /*Yeah but you can do the same with a case statement... which was /*introduced in Oracle 9i.. /*Plus there's no reason to select it unless you intend on using it later. /*Just do /* /*select class from classes /*order by decode(lower(class),'aa',1,'a',2,...) /* /*just FYI /* /*On Tue, Aug 26, 2008 at 2:31 PM, Eric Roberts /*<[EMAIL PROTECTED]> wrote: /*> There is also a way in Oracle(just for reference)...you can used /*decode() to /*> do this (not sure if any of the other db's have this or a similar /*function). /*> /*> You would add decode(class,'aa',1,'a',2,'b',3...,/default value/) as /*> sort_order in the select statement then in the order by, order by /*sort_order /*> /*> Eric /*> /*> /*-Original Message- /*> /*From: Rick Faircloth [mailto:[EMAIL PROTECTED] /*> /*Sent: Tuesday, August 26, 2008 2:10 PM /*> /*To: CF-Talk /*> /*Subject: RE: Here's an interesting little problem... /*> /* /*> /*It's already in the mail! :o) /*> /* /*> /*I definitely put yours and Steve's solutions /*> /*in my snippets. Steve's will work when value length /*> /*is a determinant, but yours will work in any "case". :o) /*> /* /*> /*Rick /*> /* /*> /* /*> /*> -Original Message- /*> /*> From: Charlie Griefer [mailto:[EMAIL PROTECTED] /*> /*> Sent: Tuesday, August 26, 2008 2:44 PM /*> /*> To: CF-Talk /*> /*> Subject: Re: Here's an interesting little problem... /*> /*> /*> /*> i'm not givin' back my prize. /*> /*> /*> /*> On Tue, Aug 26, 2008 at 11:38 AM, Rick Faircloth /*> /*> <[EMAIL PROTECTED]>wrote: /*> /*> /*> /*> > And we have another winner! Steve! /*> /*> > With an even faster way to skin the cat. (My apologies /*> /*> > to my cat, Maggie...) /*> /*> > /*> /*> > /*> /*> > /*> /*> > select t.name, t.date, t.time, /*> /*> >s.firstname, s.lastname, s.state, s.nsca, /*> /*> >r.con, r.class, r.shot, r.broke /*> /*> > from tournaments t, shooters s, registrations r /*> /*> > where r.shooter_id = s.shooter_id /*> /*> > andr.tournament_id = t.tournament_id /*> /*> > andt.tournament_id = /*> > value="#url.tournament_id#"> /*> /*> > order by length(class) desc, class /*> /*> > /*> /*> > /*> /*> > /*> /* /*> /* /*> /* /*> /* /*> /*> /* /* ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311631 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Here's an interesting little problem...
i know i'm a little late to this party but... SELECT `class`, concat(left(`class`, 1),100-length(`class`)) AS `sort` FROM myTable ORDER BY `sort` ASC will give you AAA AA A B CC C D etc ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311629 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Here's an interesting little problem...
Yeah but you can do the same with a case statement... which was introduced in Oracle 9i.. Plus there's no reason to select it unless you intend on using it later. Just do select class from classes order by decode(lower(class),'aa',1,'a',2,...) just FYI On Tue, Aug 26, 2008 at 2:31 PM, Eric Roberts <[EMAIL PROTECTED]> wrote: > There is also a way in Oracle(just for reference)...you can used decode() to > do this (not sure if any of the other db's have this or a similar function). > > You would add decode(class,'aa',1,'a',2,'b',3...,/default value/) as > sort_order in the select statement then in the order by, order by sort_order > > Eric > > /*-Original Message- > /*From: Rick Faircloth [mailto:[EMAIL PROTECTED] > /*Sent: Tuesday, August 26, 2008 2:10 PM > /*To: CF-Talk > /*Subject: RE: Here's an interesting little problem... > /* > /*It's already in the mail! :o) > /* > /*I definitely put yours and Steve's solutions > /*in my snippets. Steve's will work when value length > /*is a determinant, but yours will work in any "case". :o) > /* > /*Rick > /* > /* > /*> -----Original Message- > /*> From: Charlie Griefer [mailto:[EMAIL PROTECTED] > /*> Sent: Tuesday, August 26, 2008 2:44 PM > /*> To: CF-Talk > /*> Subject: Re: Here's an interesting little problem... > /*> > /*> i'm not givin' back my prize. > /*> > /*> On Tue, Aug 26, 2008 at 11:38 AM, Rick Faircloth > /*> <[EMAIL PROTECTED]>wrote: > /*> > /*> > And we have another winner! Steve! > /*> > With an even faster way to skin the cat. (My apologies > /*> > to my cat, Maggie...) > /*> > > /*> > > /*> > > /*> > select t.name, t.date, t.time, > /*> >s.firstname, s.lastname, s.state, s.nsca, > /*> >r.con, r.class, r.shot, r.broke > /*> > from tournaments t, shooters s, registrations r > /*> > where r.shooter_id = s.shooter_id > /*> > andr.tournament_id = t.tournament_id > /*> > andt.tournament_id = /*> > value="#url.tournament_id#"> > /*> > order by length(class) desc, class > /*> > > /*> > > /*> > > /* > /* > /* > /* > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311628 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Here's an interesting little problem...
There is also a way in Oracle(just for reference)...you can used decode() to do this (not sure if any of the other db's have this or a similar function). You would add decode(class,'aa',1,'a',2,'b',3...,/default value/) as sort_order in the select statement then in the order by, order by sort_order Eric /*-Original Message- /*From: Rick Faircloth [mailto:[EMAIL PROTECTED] /*Sent: Tuesday, August 26, 2008 2:10 PM /*To: CF-Talk /*Subject: RE: Here's an interesting little problem... /* /*It's already in the mail! :o) /* /*I definitely put yours and Steve's solutions /*in my snippets. Steve's will work when value length /*is a determinant, but yours will work in any "case". :o) /* /*Rick /* /* /*> -Original Message- /*> From: Charlie Griefer [mailto:[EMAIL PROTECTED] /*> Sent: Tuesday, August 26, 2008 2:44 PM /*> To: CF-Talk /*> Subject: Re: Here's an interesting little problem... /*> /*> i'm not givin' back my prize. /*> /*> On Tue, Aug 26, 2008 at 11:38 AM, Rick Faircloth /*> <[EMAIL PROTECTED]>wrote: /*> /*> > And we have another winner! Steve! /*> > With an even faster way to skin the cat. (My apologies /*> > to my cat, Maggie...) /*> > /*> > /*> > /*> > select t.name, t.date, t.time, /*> >s.firstname, s.lastname, s.state, s.nsca, /*> >r.con, r.class, r.shot, r.broke /*> > from tournaments t, shooters s, registrations r /*> > where r.shooter_id = s.shooter_id /*> > andr.tournament_id = t.tournament_id /*> > andt.tournament_id = > value="#url.tournament_id#"> /*> > order by length(class) desc, class /*> > /*> > /*> > /* /* /* /* ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311627 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Here's an interesting little problem...
It's already in the mail! :o) I definitely put yours and Steve's solutions in my snippets. Steve's will work when value length is a determinant, but yours will work in any "case". :o) Rick > -Original Message- > From: Charlie Griefer [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 26, 2008 2:44 PM > To: CF-Talk > Subject: Re: Here's an interesting little problem... > > i'm not givin' back my prize. > > On Tue, Aug 26, 2008 at 11:38 AM, Rick Faircloth > <[EMAIL PROTECTED]>wrote: > > > And we have another winner! Steve! > > With an even faster way to skin the cat. (My apologies > > to my cat, Maggie...) > > > > > > > > select t.name, t.date, t.time, > >s.firstname, s.lastname, s.state, s.nsca, > >r.con, r.class, r.shot, r.broke > > from tournaments t, shooters s, registrations r > > where r.shooter_id = s.shooter_id > > andr.tournament_id = t.tournament_id > > andt.tournament_id = > value="#url.tournament_id#"> > > order by length(class) desc, class > > > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311625 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Here's an interesting little problem...
i'm not givin' back my prize. On Tue, Aug 26, 2008 at 11:38 AM, Rick Faircloth <[EMAIL PROTECTED]>wrote: > And we have another winner! Steve! > With an even faster way to skin the cat. (My apologies > to my cat, Maggie...) > > > > select t.name, t.date, t.time, >s.firstname, s.lastname, s.state, s.nsca, >r.con, r.class, r.shot, r.broke > from tournaments t, shooters s, registrations r > where r.shooter_id = s.shooter_id > andr.tournament_id = t.tournament_id > andt.tournament_id = value="#url.tournament_id#"> > order by length(class) desc, class > > > > > -Original Message- > > From: Milburn, Steve [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, August 26, 2008 2:15 PM > > To: CF-Talk > > Subject: RE: Here's an interesting little problem... > > > > Sorry, I just saw the post where you stated you are using MySQL. len is > not a MySQL function, but > > length is. try this: > > > > order by length(class) desc, class. > > > > Steve > > > > ________ > > From: Milburn, Steve [EMAIL PROTECTED] > > Sent: Tuesday, August 26, 2008 2:07 PM > > To: CF-Talk > > Subject: RE: Here's an interesting little problem... > > > > How about this: > > > > order by len(class) desc, class > > > > ? > > > > Steve > > > > > > From: Rick Faircloth [EMAIL PROTECTED] > > Sent: Tuesday, August 26, 2008 2:00 PM > > To: CF-Talk > > Subject: RE: Here's an interesting little problem... > > > > AA > > A > > B > > C > > D > > E > > etc. > > > > > > > -Original Message- > > > From: Greg Morphis [mailto:[EMAIL PROTECTED] > > > Sent: Tuesday, August 26, 2008 1:41 PM > > > To: CF-Talk > > > Subject: Re: Here's an interesting little problem... > > > > > > Rick, did you want to see > > > AA > > > D > > > C > > > B > > > A > > > > > > or > > > > > > D > > > C > > > B > > > A > > > AA > > > > > > ? > > > > > > > > > > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311621 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Here's an interesting little problem...
And we have another winner! Steve! With an even faster way to skin the cat. (My apologies to my cat, Maggie...) select t.name, t.date, t.time, s.firstname, s.lastname, s.state, s.nsca, r.con, r.class, r.shot, r.broke from tournaments t, shooters s, registrations r where r.shooter_id = s.shooter_id andr.tournament_id = t.tournament_id andt.tournament_id = order by length(class) desc, class > -Original Message- > From: Milburn, Steve [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 26, 2008 2:15 PM > To: CF-Talk > Subject: RE: Here's an interesting little problem... > > Sorry, I just saw the post where you stated you are using MySQL. len is not > a MySQL function, but > length is. try this: > > order by length(class) desc, class. > > Steve > > > From: Milburn, Steve [EMAIL PROTECTED] > Sent: Tuesday, August 26, 2008 2:07 PM > To: CF-Talk > Subject: RE: Here's an interesting little problem... > > How about this: > > order by len(class) desc, class > > ? > > Steve > > > From: Rick Faircloth [EMAIL PROTECTED] > Sent: Tuesday, August 26, 2008 2:00 PM > To: CF-Talk > Subject: RE: Here's an interesting little problem... > > AA > A > B > C > D > E > etc. > > > > -----Original Message----- > > From: Greg Morphis [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, August 26, 2008 1:41 PM > > To: CF-Talk > > Subject: Re: Here's an interesting little problem... > > > > Rick, did you want to see > > AA > > D > > C > > B > > A > > > > or > > > > D > > C > > B > > A > > AA > > > > ? > > > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311620 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Here's an interesting little problem...
Thanks, Dave!
Rick
> -Original Message-
> From: Dave Phillips [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 26, 2008 2:16 PM
> To: CF-Talk
> Subject: RE: Here's an interesting little problem...
>
> Because the column name will be represented by whatever the FIRST select is,
> and in this case, that is 'top_shooter_class'. Try this:
>
>
>
> select t.name, t.date, t.time,
> s.firstname, s.lastname, s.state, s.nsca,
> r.con, r.class, r.shot, r.broke
> from tournaments t, shooters s, registrations r
> where r.shooter_id = s.shooter_id
> andr.class in ('A', 'AA', 'AAA')
> andr.tournament_id = t.tournament_id
> andt.tournament_id = value="#url.tournament_id#">
> order by class, broke desc
>
> union all
>
> select t.name, t.date, t.time,
> s.firstname, s.lastname, s.state, s.nsca,
> r.con, r.class, r.shot, r.broke
> from tournaments t, shooters s, registrations r
> where r.shooter_id = s.shooter_id
> andr.class not in ('A', 'AA', 'AAA')
>
> andr.tournament_id = t.tournament_id
> andt.tournament_id = value="#url.tournament_id#">
>
> order byclass desc, broke desc
>
>
>
> If that doesn't work, try some parentheses around each of the selects. If
> that still doesn't work, last resort you can do this:
>
>
>
> select t.name, t.date, t.time,
> s.firstname, s.lastname, s.state, s.nsca,
> r.con, r.class, r.shot, r.broke
> from tournaments t, shooters s, registrations r
> where r.shooter_id = s.shooter_id
> andr.class in ('A', 'AA', 'AAA')
> andr.tournament_id = t.tournament_id
> andt.tournament_id = value="#url.tournament_id#">
> order by class, broke desc
>
>
>
> select t.name, t.date, t.time,
> s.firstname, s.lastname, s.state, s.nsca,
> r.con, r.class, r.shot, r.broke
> from tournaments t, shooters s, registrations r
> where r.shooter_id = s.shooter_id
> andr.class not in ('A', 'AA', 'AAA')
>
> andr.tournament_id = t.tournament_id
> andt.tournament_id = value="#url.tournament_id#">
>
> order byclass desc, broke desc
>
>
>
> Select * from get_results1
> Union all
> Select * from get_results2
>
>
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311618
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Here's an interesting little problem...
We have a winner! Charlie! Solution: select t.name, t.date, t.time, s.firstname, s.lastname, s.state, s.nsca, r.con, r.class, r.shot, r.broke from tournaments t, shooters s, registrations r where r.shooter_id = s.shooter_id andr.tournament_id = t.tournament_id andt.tournament_id = order by case class when 'AAA' then 10 when 'AA' then 20 when 'A' then 30 when 'B' then 40 when 'C' then 50 when 'D' then 60 when 'E' then 70 when 'F' then 80 when 'G' then 90 when 'H' then 100 when 'I' then 110 when 'J' then 120 when 'K' then 130 end, r.broke desc Thanks, Charlie and everyone! Rick > -Original Message----- > From: Charlie Griefer [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 26, 2008 2:12 PM > To: CF-Talk > Subject: Re: Here's an interesting little problem... > > ORDER BY > CASE class > WHEN 'AA' THEN 10 > WHEN 'A' THEN 20 > WHEN 'B' THEN 30 > WHEN 'C' THEN 40 > WHEN 'D' THEN 50 > WHEN 'E' THEN 60 > END > > incremented by 10 so you can add 'AAA' etc later :) > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311616 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Here's an interesting little problem...
Sorry, I just saw the post where you stated you are using MySQL. len is not a MySQL function, but length is. try this: order by length(class) desc, class. Steve From: Milburn, Steve [EMAIL PROTECTED] Sent: Tuesday, August 26, 2008 2:07 PM To: CF-Talk Subject: RE: Here's an interesting little problem... How about this: order by len(class) desc, class ? Steve From: Rick Faircloth [EMAIL PROTECTED] Sent: Tuesday, August 26, 2008 2:00 PM To: CF-Talk Subject: RE: Here's an interesting little problem... AA A B C D E etc. > -Original Message- > From: Greg Morphis [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 26, 2008 1:41 PM > To: CF-Talk > Subject: Re: Here's an interesting little problem... > > Rick, did you want to see > AA > D > C > B > A > > or > > D > C > B > A > AA > > ? > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311615 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Here's an interesting little problem...
Because the column name will be represented by whatever the FIRST select is,
and in this case, that is 'top_shooter_class'. Try this:
select t.name, t.date, t.time,
s.firstname, s.lastname, s.state, s.nsca,
r.con, r.class, r.shot, r.broke
from tournaments t, shooters s, registrations r
where r.shooter_id = s.shooter_id
andr.class in ('A', 'AA', 'AAA')
andr.tournament_id = t.tournament_id
andt.tournament_id =
order by class, broke desc
union all
select t.name, t.date, t.time,
s.firstname, s.lastname, s.state, s.nsca,
r.con, r.class, r.shot, r.broke
from tournaments t, shooters s, registrations r
where r.shooter_id = s.shooter_id
andr.class not in ('A', 'AA', 'AAA')
andr.tournament_id = t.tournament_id
andt.tournament_id =
order by class desc, broke desc
If that doesn't work, try some parentheses around each of the selects. If
that still doesn't work, last resort you can do this:
select t.name, t.date, t.time,
s.firstname, s.lastname, s.state, s.nsca,
r.con, r.class, r.shot, r.broke
from tournaments t, shooters s, registrations r
where r.shooter_id = s.shooter_id
andr.class in ('A', 'AA', 'AAA')
andr.tournament_id = t.tournament_id
andt.tournament_id =
order by class, broke desc
select t.name, t.date, t.time,
s.firstname, s.lastname, s.state, s.nsca,
r.con, r.class, r.shot, r.broke
from tournaments t, shooters s, registrations r
where r.shooter_id = s.shooter_id
andr.class not in ('A', 'AA', 'AAA')
andr.tournament_id = t.tournament_id
andt.tournament_id =
order by class desc, broke desc
Select * from get_results1
Union all
Select * from get_results2
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311614
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Here's an interesting little problem...
ORDER BY
CASE class
WHEN 'AA' THEN 10
WHEN 'A' THEN 20
WHEN 'B' THEN 30
WHEN 'C' THEN 40
WHEN 'D' THEN 50
WHEN 'E' THEN 60
END
incremented by 10 so you can add 'AAA' etc later :)
On Tue, Aug 26, 2008 at 11:00 AM, Rick Faircloth
<[EMAIL PROTECTED]>wrote:
> AA
> A
> B
> C
> D
> E
> etc.
>
> > -Original Message-
> > From: Greg Morphis [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, August 26, 2008 1:41 PM
> > To: CF-Talk
> > Subject: Re: Here's an interesting little problem...
> >
> > Rick, did you want to see
> > AA
> > D
> > C
> > B
> > A
> >
> > or
> >
> > D
> > C
> > B
> > A
> > AA
> >
> > ?
> >
> > On Tue, Aug 26, 2008 at 12:25 PM, Rick Faircloth
> > <[EMAIL PROTECTED]> wrote:
> > > Sounds like a good idea to me.
> > >
> > > I'll give it a shot.
> > >
> > > Thanks,
> > >
> > > Rick
> > >
> > >> -Original Message-
> > >> From: Dave Phillips [mailto:[EMAIL PROTECTED]
> > >> Sent: Tuesday, August 26, 2008 12:44 PM
> > >> To: CF-Talk
> > >> Subject: RE: Here's an interesting little problem...
> > >>
> > >> Rick,
> > >>
> > >> What database are you using?
> > >>
> > >> One thought that comes to mind is to do TWO selects with a UNION and
> give
> > >> each their own ORDER BY clause. Something like this:
> > >>
> > >> Select . where class NOT in ('A','AA','AAA') ORDER BY class DESC
> > >> UNION ALL
> > >> Select . where class in ('A','AA','AAA') ORDER BY class ASC
> > >>
> > >> You might need some parenthesis around the queries to make it work,
> not sure
> > >> which DB you're using.
> > >>
> > >> At least that might get you on the right track.
> > >>
> > >> I don't think a CASE statement would work in this 'case' (no pun
> intended),
> > >> but I've never tried one in an ORDER BY. Might be worth a shot.
> > >>
> > >> Dave
> > >>
> > >> -Original Message-
> > >> From: Rick Faircloth [mailto:[EMAIL PROTECTED]
> > >> Sent: Tuesday, August 26, 2008 11:31 AM
> > >> To: CF-Talk
> > >> Subject: Here's an interesting little problem...
> > >>
> > >> I need to order query results by class desc.
> > >>
> > >> Classes are typically AA, A, B, C, D, etc.
> > >>
> > >> Order by class desc puts the AA, A in the correct order,
> > >> but when all are ordered, I get:
> > >>
> > >> D
> > >> C
> > >> B
> > >> AA
> > >> A
> > >>
> > >> The only double letters involved in classes are AA, and AAA.
> > >>
> > >> I need a conditional "order by" statement...
> > >>
> > >> Order by class desc where class is AA or AAA,
> > >> otherwise order by class asc
> > >>
> > >> Suggestions?
> > >>
> > >> Thanks,
> > >>
> > >> Rick
> > >>
> > >>
> > >>
> > >
> > >
> >
> >
>
>
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311612
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Here's an interesting little problem...
How about this: order by len(class) desc, class ? Steve From: Rick Faircloth [EMAIL PROTECTED] Sent: Tuesday, August 26, 2008 2:00 PM To: CF-Talk Subject: RE: Here's an interesting little problem... AA A B C D E etc. > -Original Message- > From: Greg Morphis [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 26, 2008 1:41 PM > To: CF-Talk > Subject: Re: Here's an interesting little problem... > > Rick, did you want to see > AA > D > C > B > A > > or > > D > C > B > A > AA > > ? > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311611 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Here's an interesting little problem...
Hmmm... why would I get this error: (MySQL 5)
Unknown column 'lower_shooter_class' in 'order clause'
from this query:
select t.name, t.date, t.time,
s.firstname, s.lastname, s.state, s.nsca,
r.con, r.class as top_shooter_class, r.shot, r.broke as
top_broke
from tournaments t, shooters s, registrations r
where r.shooter_id = s.shooter_id
andr.class in ('A', 'AA', 'AAA')
andr.tournament_id = t.tournament_id
andt.tournament_id =
union all
select t.name, t.date, t.time,
s.firstname, s.lastname, s.state, s.nsca,
r.con, r.class as lower_shooter_class, r.shot, r.broke as
lower_broke
from tournaments t, shooters s, registrations r
where r.shooter_id = s.shooter_id
andr.class not in ('A', 'AA', 'AAA')
andr.tournament_id = t.tournament_id
andt.tournament_id =
order by top_shooter_class desc, top_broke desc, lower_shooter_class,
lower_broke desc
> -Original Message-
> From: Dave Phillips [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 26, 2008 12:44 PM
> To: CF-Talk
> Subject: RE: Here's an interesting little problem...
>
> Rick,
>
> What database are you using?
>
> One thought that comes to mind is to do TWO selects with a UNION and give
> each their own ORDER BY clause. Something like this:
>
> Select . where class NOT in ('A','AA','AAA') ORDER BY class DESC
> UNION ALL
> Select . where class in ('A','AA','AAA') ORDER BY class ASC
>
> You might need some parenthesis around the queries to make it work, not sure
> which DB you're using.
>
> At least that might get you on the right track.
>
> I don't think a CASE statement would work in this 'case' (no pun intended),
> but I've never tried one in an ORDER BY. Might be worth a shot.
>
> Dave
>
> -Original Message-
> From: Rick Faircloth [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 26, 2008 11:31 AM
> To: CF-Talk
> Subject: Here's an interesting little problem...
>
> I need to order query results by class desc.
>
> Classes are typically AA, A, B, C, D, etc.
>
> Order by class desc puts the AA, A in the correct order,
> but when all are ordered, I get:
>
> D
> C
> B
> AA
> A
>
> The only double letters involved in classes are AA, and AAA.
>
> I need a conditional "order by" statement...
>
> Order by class desc where class is AA or AAA,
> otherwise order by class asc
>
> Suggestions?
>
> Thanks,
>
> Rick
>
>
>
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311610
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Here's an interesting little problem...
AA
A
B
C
D
E
etc.
> -Original Message-
> From: Greg Morphis [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 26, 2008 1:41 PM
> To: CF-Talk
> Subject: Re: Here's an interesting little problem...
>
> Rick, did you want to see
> AA
> D
> C
> B
> A
>
> or
>
> D
> C
> B
> A
> AA
>
> ?
>
> On Tue, Aug 26, 2008 at 12:25 PM, Rick Faircloth
> <[EMAIL PROTECTED]> wrote:
> > Sounds like a good idea to me.
> >
> > I'll give it a shot.
> >
> > Thanks,
> >
> > Rick
> >
> >> -Original Message-
> >> From: Dave Phillips [mailto:[EMAIL PROTECTED]
> >> Sent: Tuesday, August 26, 2008 12:44 PM
> >> To: CF-Talk
> >> Subject: RE: Here's an interesting little problem...
> >>
> >> Rick,
> >>
> >> What database are you using?
> >>
> >> One thought that comes to mind is to do TWO selects with a UNION and give
> >> each their own ORDER BY clause. Something like this:
> >>
> >> Select . where class NOT in ('A','AA','AAA') ORDER BY class DESC
> >> UNION ALL
> >> Select . where class in ('A','AA','AAA') ORDER BY class ASC
> >>
> >> You might need some parenthesis around the queries to make it work, not
> >> sure
> >> which DB you're using.
> >>
> >> At least that might get you on the right track.
> >>
> >> I don't think a CASE statement would work in this 'case' (no pun intended),
> >> but I've never tried one in an ORDER BY. Might be worth a shot.
> >>
> >> Dave
> >>
> >> -Original Message-
> >> From: Rick Faircloth [mailto:[EMAIL PROTECTED]
> >> Sent: Tuesday, August 26, 2008 11:31 AM
> >> To: CF-Talk
> >> Subject: Here's an interesting little problem...
> >>
> >> I need to order query results by class desc.
> >>
> >> Classes are typically AA, A, B, C, D, etc.
> >>
> >> Order by class desc puts the AA, A in the correct order,
> >> but when all are ordered, I get:
> >>
> >> D
> >> C
> >> B
> >> AA
> >> A
> >>
> >> The only double letters involved in classes are AA, and AAA.
> >>
> >> I need a conditional "order by" statement...
> >>
> >> Order by class desc where class is AA or AAA,
> >> otherwise order by class asc
> >>
> >> Suggestions?
> >>
> >> Thanks,
> >>
> >> Rick
> >>
> >>
> >>
> >
> >
>
>
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311607
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Here's an interesting little problem...
Rick, did you want to see
AA
D
C
B
A
or
D
C
B
A
AA
?
On Tue, Aug 26, 2008 at 12:25 PM, Rick Faircloth
<[EMAIL PROTECTED]> wrote:
> Sounds like a good idea to me.
>
> I'll give it a shot.
>
> Thanks,
>
> Rick
>
>> -Original Message-
>> From: Dave Phillips [mailto:[EMAIL PROTECTED]
>> Sent: Tuesday, August 26, 2008 12:44 PM
>> To: CF-Talk
>> Subject: RE: Here's an interesting little problem...
>>
>> Rick,
>>
>> What database are you using?
>>
>> One thought that comes to mind is to do TWO selects with a UNION and give
>> each their own ORDER BY clause. Something like this:
>>
>> Select . where class NOT in ('A','AA','AAA') ORDER BY class DESC
>> UNION ALL
>> Select . where class in ('A','AA','AAA') ORDER BY class ASC
>>
>> You might need some parenthesis around the queries to make it work, not sure
>> which DB you're using.
>>
>> At least that might get you on the right track.
>>
>> I don't think a CASE statement would work in this 'case' (no pun intended),
>> but I've never tried one in an ORDER BY. Might be worth a shot.
>>
>> Dave
>>
>> -Original Message-
>> From: Rick Faircloth [mailto:[EMAIL PROTECTED]
>> Sent: Tuesday, August 26, 2008 11:31 AM
>> To: CF-Talk
>> Subject: Here's an interesting little problem...
>>
>> I need to order query results by class desc.
>>
>> Classes are typically AA, A, B, C, D, etc.
>>
>> Order by class desc puts the AA, A in the correct order,
>> but when all are ordered, I get:
>>
>> D
>> C
>> B
>> AA
>> A
>>
>> The only double letters involved in classes are AA, and AAA.
>>
>> I need a conditional "order by" statement...
>>
>> Order by class desc where class is AA or AAA,
>> otherwise order by class asc
>>
>> Suggestions?
>>
>> Thanks,
>>
>> Rick
>>
>>
>>
>
>
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311603
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Here's an interesting little problem...
I'd rather avoid the extra column for numeric sorting.
My client is terribly lazy and would see that as just
one more thing to do to maintain the app. G...
Prepending the spaces sounds interesting.
I may try that. I can do that on the fly, with
concat(A, " "), etc... Well actually concat won't work
since that will append instead of prepend the spaces.
I'll have to check. Maybe concat(" ", A) would work.
Thanks,
Rick
> -Original Message-
> From: Carl Von Stetten [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 26, 2008 12:53 PM
> To: CF-Talk
> Subject: Re: Here's an interesting little problem...
>
> Rick,
>
> Could you add an additional column to the database table to store a
> numeric sorting value? Or a second character column with spaces
> prepended to the single character and double character values (like "
> A", " B", " AA", "AAA")?
>
> Carl
>
> Rick Faircloth wrote:
> > I need to order query results by class desc.
> >
> > Classes are typically AA, A, B, C, D, etc.
> >
> > Order by class desc puts the AA, A in the correct order,
> > but when all are ordered, I get:
> >
> > D
> > C
> > B
> > AA
> > A
> >
> > The only double letters involved in classes are AA, and AAA.
> >
> > I need a conditional "order by" statement...
> >
> > Order by class desc where class is AA or AAA,
> > otherwise order by class asc
> >
> > Suggestions?
> >
> > Thanks,
> >
> > Rick
> >
> >
> >
>
>
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311601
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Here's an interesting little problem...
Oh, sorry... MySQL 5.
> -Original Message-
> From: Dave Phillips [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 26, 2008 12:44 PM
> To: CF-Talk
> Subject: RE: Here's an interesting little problem...
>
> Rick,
>
> What database are you using?
>
> One thought that comes to mind is to do TWO selects with a UNION and give
> each their own ORDER BY clause. Something like this:
>
> Select . where class NOT in ('A','AA','AAA') ORDER BY class DESC
> UNION ALL
> Select . where class in ('A','AA','AAA') ORDER BY class ASC
>
> You might need some parenthesis around the queries to make it work, not sure
> which DB you're using.
>
> At least that might get you on the right track.
>
> I don't think a CASE statement would work in this 'case' (no pun intended),
> but I've never tried one in an ORDER BY. Might be worth a shot.
>
> Dave
AM
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311600
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Here's an interesting little problem...
Sounds like a good idea to me.
I'll give it a shot.
Thanks,
Rick
> -Original Message-
> From: Dave Phillips [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 26, 2008 12:44 PM
> To: CF-Talk
> Subject: RE: Here's an interesting little problem...
>
> Rick,
>
> What database are you using?
>
> One thought that comes to mind is to do TWO selects with a UNION and give
> each their own ORDER BY clause. Something like this:
>
> Select . where class NOT in ('A','AA','AAA') ORDER BY class DESC
> UNION ALL
> Select . where class in ('A','AA','AAA') ORDER BY class ASC
>
> You might need some parenthesis around the queries to make it work, not sure
> which DB you're using.
>
> At least that might get you on the right track.
>
> I don't think a CASE statement would work in this 'case' (no pun intended),
> but I've never tried one in an ORDER BY. Might be worth a shot.
>
> Dave
>
> -Original Message-
> From: Rick Faircloth [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 26, 2008 11:31 AM
> To: CF-Talk
> Subject: Here's an interesting little problem...
>
> I need to order query results by class desc.
>
> Classes are typically AA, A, B, C, D, etc.
>
> Order by class desc puts the AA, A in the correct order,
> but when all are ordered, I get:
>
> D
> C
> B
> AA
> A
>
> The only double letters involved in classes are AA, and AAA.
>
> I need a conditional "order by" statement...
>
> Order by class desc where class is AA or AAA,
> otherwise order by class asc
>
> Suggestions?
>
> Thanks,
>
> Rick
>
>
>
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311599
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Here's an interesting little problem...
Rick, Could you add an additional column to the database table to store a numeric sorting value? Or a second character column with spaces prepended to the single character and double character values (like " A", " B", " AA", "AAA")? Carl Rick Faircloth wrote: > I need to order query results by class desc. > > Classes are typically AA, A, B, C, D, etc. > > Order by class desc puts the AA, A in the correct order, > but when all are ordered, I get: > > D > C > B > AA > A > > The only double letters involved in classes are AA, and AAA. > > I need a conditional "order by" statement... > > Order by class desc where class is AA or AAA, > otherwise order by class asc > > Suggestions? > > Thanks, > > Rick > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311596 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Here's an interesting little problem...
Rick,
What database are you using?
One thought that comes to mind is to do TWO selects with a UNION and give
each their own ORDER BY clause. Something like this:
Select . where class NOT in ('A','AA','AAA') ORDER BY class DESC
UNION ALL
Select . where class in ('A','AA','AAA') ORDER BY class ASC
You might need some parenthesis around the queries to make it work, not sure
which DB you're using.
At least that might get you on the right track.
I don't think a CASE statement would work in this 'case' (no pun intended),
but I've never tried one in an ORDER BY. Might be worth a shot.
Dave
-Original Message-
From: Rick Faircloth [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 26, 2008 11:31 AM
To: CF-Talk
Subject: Here's an interesting little problem...
I need to order query results by class desc.
Classes are typically AA, A, B, C, D, etc.
Order by class desc puts the AA, A in the correct order,
but when all are ordered, I get:
D
C
B
AA
A
The only double letters involved in classes are AA, and AAA.
I need a conditional "order by" statement...
Order by class desc where class is AA or AAA,
otherwise order by class asc
Suggestions?
Thanks,
Rick
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311593
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
