Group By

[enak]

I need to know how to group this sql statement.

select eh.EvaluatorID,
(select fullName from vwEmployeefull as vw where vw.TimekeeperID = eh.Evaluatorid) as Evaluator,
(select fullname from vwEmployeeFull as vw where vw.timekeeperID = eh.EvalueeID) as Evaluee,
(select OfficeAbbr from vwEmployeeFull as vw where vw.timekeeperID = eh.EvalueeID) as Office,
(select Department from akDataTransfer.dbo.HRDepartment as hr where hr.DeptID = eh.DepartmentID) as Department
from evalheader as eh
group by Evaluator

I have used the Max() function but I get errors. Does anyone know how to do this?

TIA
Nate

 

[Vancouverite]

Looking at your query, I don't think you need to Group By... I think you need to Order By.

When you Group By X, you will return only one row per X, so if you successfully Group By Evaluator, there will only be one row per Evaluator. Instead, try:

select eh.EvaluatorID, evo.FullName As Evaluator, Eve.FullName As Evaluatee, Eve.OfficeID As Office, hr.Department As Department
from evalheader as eh
Inner Join vwEmployeeFull as evo WHERE evo.TimeKeeperID = eh.EvaluatorID
Inner Join vwEmployeeFull as eve WHERE eve.TimeKeeperID = eh.EvalueeID
Inner Join akDataTransfer.dbo.HRDepartment as hr WHERE hr.DeptID = eh.DepartmentID
Order By evo.FullName