Hmmm... why would I get this error: (MySQL 5)
Unknown column 'lower_shooter_class' in 'order clause'
from this query:
<cfquery name="get_results" datasource="#application.dsn#">
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
and r.class in ('A', 'AA', 'AAA')
and r.tournament_id = t.tournament_id
and t.tournament_id = <cfqueryparam cfsqltype="integer"
value="#url.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
and r.class not in ('A', 'AA', 'AAA')
and r.tournament_id = t.tournament_id
and t.tournament_id = <cfqueryparam cfsqltype="integer"
value="#url.tournament_id#">
order by top_shooter_class desc, top_broke desc, lower_shooter_class,
lower_broke desc
</cfquery>
> -----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
