sql - Order BY on column generated by select statement -

sql - Order BY on column generated by select statement -

i'm running query:

select claims.rid ,claims.client ,clients.cname ,cur.currency ,carriers.scac ,carriers.cname ,claims.client+claims.counter ,claims.dateon ,(select top 1 errorcode entries entries.rid=claims.rid) errorcode ,(select sum(refunddue) entries entries.rid=claims.rid) amount ,auditors.initials claims inner bring together clients on clients.code = claims.client inner bring together currency cur on claims.currency = cur.currencyid inner bring together entries on claims.rid = entries.rid inner bring together carriers on carriers.carrierid = claims.carrierid inner bring together auditors on claims.auditorid=auditors.auditorid grouping claims.rid ,claims.client ,claims.counter ,claims.dateon ,carriers.scac ,carriers.cname ,clients.cname ,cur.currency ,auditors.initials ,errorcode order errorcode asc

the focus should on orderby errorcode. reason, it's not ordering alphabetically errorcode. ideas why?

most databases don't allow utilize aliases created in select-list in grouping by, order , clauses. instead, have repeat look used in select-list:

select claims.rid, ..., (select top 1 errorcode entries entries.rid=claims.rid) errorcode, ... claims inner bring together ... grouping claims.rid, ..., (select top 1 errorcode entries entries.rid=claims.rid) order (select top 1 errorcode entries entries.rid=claims.rid) asc

but think improve utilize sub-select info source in from-clause:

select claims.rid, ..., e.errcode, e.amount, ... claims inner bring together clients on clients.code = claims.client inner bring together currency cur on claims.currency = cur.currencyid inner bring together (select rid, min(errorcode) errcode, sum(refunddue) amount entries grouping rid) e on claims.rid = e.rid inner bring together carriers on carriers.carrierid = claims.carrierid inner bring together auditors on claims.auditorid=auditors.auditorid grouping claims.rid, ..., e.errcode order e.errcode asc

note included sum(refunddue) amount well. if error codes can null within entries, utilize max(errorcode) instead of min(errorcode).

sql