Having a slight problem with a query using COALESCE
Theres an integer column to rank the attorneys in order. Usually only a
few a ranked - the others have a null in the rank column. The ranked
attorneys need to appear first in the results.
Some attorneys are in the table more than once, so thusly the use of
distinct, which is causing the problem. If I remove it, the query works,
but Bob Smith is going to show up four times...
SELECT distinct(atty_id),
first_name,
last_name,
atty_rank FROM get_attypa
WHERE isACTIVE = 'y'
ORDER BY COALESCE(atty_rank, 9999) asc, last_name, first_name ASC
But - I'm getting the error below:
"ORDER BY items must appear in the select list if SELECT DISTINCT is
specified."
Suggestions?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305162
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
