RBarryYoung (5/13/2009)

---

Nish (5/13/2009)

---

Sorry for not explaining properly,

To sort on Jobcount , i have to write "(ORDER BY j.JobCount)" , but as you said datatable does not know the table prifix, same with sort on company i'll have to pass "(ORDER BY C.CompanyId" )" , so i cant know the table alias on the runtime. is there any workaround, i guess not. but anyother method where i dont have to pass the table alias.

No you don't. ORDER BY columns in fact are supposed to use thier output names and not their input names. So using Flo's example, this works fine:

; WITH JobCount AS (

SELECT

CompanyId,

COUNT(*) JobCount

FROM @Job

GROUP BY CompanyId

), Cust AS (

SELECT

c.CompanyId,

j.JobCount,

ROW_NUMBER() OVER (ORDER BY j.JobCount) AS RowNumber

FROM @Company c

JOIN JobCount j ON c.CompanyId = j.CompanyId

)

SELECT

*

FROM Cust

-->

ORDER BY JobCount--this works

-->