Les,
Assuming you are using MS SQL Server, try this:
SELECT DISTINCT atty_id,
first_name,
last_name,
COALESCE(atty_rank, 9999) as atty_rank
FROM get_attypa
WHERE isACTIVE = 'y'
ORDER BY atty_rank, last_name, first_name
HTH,
Carl
Les Mizzell wrote:
> 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:305169
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
