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
-->
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]