SQLTeam.com | Weblogs | Forums

Sort sub select?

sql2012

#1

How to I sort the Analyst asc?

How it comes out:

SMALL, LISA G (98751),MICHAELS, THOMAS g (43276)

How I need it:
MICHAELS, THOMAS G (43276),SMALL, LISA G (98751)

select
[t1].[IDNum] [ID #],
[t4].[AnalystsName] [Analysts]
from
tblTrkRecord t1
join
(
select
r.Id,
r.IdNum,
ROW_NUMBER() over (partition
by
IdNum
order by
case
when s.Name = 'Cancelled' then 0
else 1
end desc,
RevNum desc) row
from
tblTrkRecord r
join
tblTrkStatus s
on s.Id = r.StatusId
) t2
on t2.row=1
and t2.Id=t1.Id
left join
tblTrkRecord t3
on t3.IdNum=t2.IdNum
left join
(
Select
tr.IdNum,
tr.Id,
STUFF((SELECT
',' + dbo.FormatEmpNameEmpId(Lastname,
FirstName,
MiddleInitial,
NameSuffix,
e.Id)
from
dbo.tblEmployee e
INNER JOIN
dbo.tblTrkRecordAnalyst AS tra
ON tra.EmployeeId = e.Id
where
tr.Id = tra.RecordId FOR XML PATH(''), TYPE).value('.',
'NVARCHAR(MAX)'),
1,
1,
'') AnalystsName
FROM
dbo.tblTrkRecord AS tr) t4
on t4.IdNum = t1.IdNum
where
(
(
t1.archived=0
)
and (
t1.IDNum like '208209%'
)
)
group by
[t1].[IDNum],
t1.idnum,
t4.AnalystsName
order by
t1.idnum,
t4.AnalystsName


#2

Put this:

order by e.lastname
        ,e.firstname
        ,e.middleinitial
        ,e.id

before:

FOR XML PATH(''), TYPE.....

ps.: Please consider formating your queries