sql - "ORDER BY items must appear in the select list if SELECT DISTINCT" when selecting from aliased subquery -

getting error running query:

select distinct top 100      t.number,     t.lastname,      t.firstname (     select top 1700 -- 100 * (mean avg # site per pi + 3sd)         pi.number,           pi.lastname,          pi.firstname             sites s     inner join           pi     on         s.pi = pi.number     inner join          protocol p      on          pi.protocolnumber = p.protocolnumber     inner join          productmaster pm      on          pa.productcode = pm.productcode             -- predicates ) t      order     t.lastname collate latin1_general_ci_ai 

i'm not sure why isn't working. i've got last name in select list. missing?

you need put collation in `select:

select distinct top 100 t.number, t.lastname collate latin1_general_ci_ai, t.firstname 

this rather insignificant change unless have case sensitive collation , want see duplicates.

if so, use group by , lower() (or upper()):

select top 100 t.number, t.lastname, t.firstname (select top 1700 -- 100 * (mean avg # site per pi + 3sd)              pi.number,  pi.lastname, pi.firstname       sites s inner join              pi            on s.pi = pi.number inner join             protocol p            on pi.protocolnumber = p.protocolnumberinner join             productmaster pm             on pa.productcode = pm.productcode       -- predicates      ) t  group t.number, t.lastname t.firstname  order max(lower(t.lastname));