SQL to Get The Top Details of For Each Record

I need to formulate an SQL. To express what I require better, I will illustrate the same using the following data:

Have to rephrase the question and the example for better understanding

T_Employee
ID Name
1  John
2  Jane
3  Joe

T_Roles
ID RoleName
1  Clerk I
2  Clerk II
3  Manager
4  Senior Manager

T_EmployeeRoles
ID EmployeeID RoleID
开发者_如何学Go1  1          1  
2  1          2
3  1          3
4  2          1
5  2          2
6  2          3
7  2          4
8  3          3
9  4          4

Now, the SQL select should be able to produce the following output:

ID  Name    Current Role     Last Role
1   John    Manager          Clerk II           
2   Jane    Senior Manager   Manager
3   Joe     Senior Manager   Manager

Any help or idea much appreciated. Thanks.

---

You could just select everything from T_EmployeeRole and add an additional RoleNumber column, using the ROW_NUMBER function to number the roles for each person like this:

ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ID desc) as RoleNumber

This restart number for each Name, giving the highest ID a RoleNumber of 1. Everything with RoleNumber = 1 would be the current role, everything with RoleNumber = 2 would be the previous role.

http://msdn.microsoft.com/en-us/library/ms186734.aspx

EDIT: To match you updated question, note in your data Joe is only a Manager and has no previous role so your example answer does not match the data.

select 1 as ID, 'John' as Name
into #T_Employee
union select 2, 'Jane'
union select 3, 'Joe'

select 1 as ID, 'Clerk I' as RoleName
into #T_Roles
union select 2, 'Clerk II'
union select 3, 'Manager'
union select 4, 'Senior Manager'

select 1 as ID, 1 as EmployeeID, 1 as RoleID
into #T_EmployeeRoles
union select 2, 1,  2
union select 3, 1,  3
union select 4, 2,  1
union select 5, 2,  2
union select 6, 2,  3
union select 7, 2,  4
union select 8, 3,  3
union select 9, 4,  4

select  er.ID
        ,er.EmployeeID
        ,er.RoleID
        ,r.RoleName
        ,ROW_NUMBER() OVER(PARTITION BY er.EmployeeID ORDER BY er.ID desc) as RoleNumber
into #OrderedRoles
from #T_EmployeeRoles er
left join #T_Roles r on r.ID = er.RoleID

select  emp.ID
        ,emp.Name
        ,r1.RoleName as CurrentRole
        ,r2.RoleName as LastRole
from #T_Employee emp
left join #OrderedRoles r1  on  r1.EmployeeID = emp.ID
                            and r1.RoleNumber = 1
left join #OrderedRoles r2  on  r2.EmployeeID = emp.ID
                            and r2.RoleNumber = 2

---

the answer by Alex works for me here is a different way, without ROW_Number...

I think your sample output is wrong, give this a try:

SET NOCOUNT ON
DECLARE @T_Employee  table (ID int, Name varchar(10))
INSERT @T_Employee VALUES(1,  'John')
INSERT @T_Employee VALUES(2,  'Jane')
INSERT @T_Employee VALUES(3,  'Joe')

DECLARE @T_Roles table (ID int, RoleName varchar(15))
INSERT @T_Roles VALUES(1,  'Clerk I')
INSERT @T_Roles VALUES(2,  'Clerk II')
INSERT @T_Roles VALUES(3,  'Manager')
INSERT @T_Roles VALUES(4,  'Senior Manager')

DECLARE @T_EmployeeRoles table (ID int, EmployeeID int, RoleID int)
INSERT @T_EmployeeRoles VALUES(1,  1,          1) 
INSERT @T_EmployeeRoles VALUES(2,  1,          2)
INSERT @T_EmployeeRoles VALUES(3,  1,          3)
INSERT @T_EmployeeRoles VALUES(4,  2,          1)
INSERT @T_EmployeeRoles VALUES(5,  2,          2)
INSERT @T_EmployeeRoles VALUES(6,  2,          3)
INSERT @T_EmployeeRoles VALUES(7,  2,          4)
INSERT @T_EmployeeRoles VALUES(8,  3,          3)
INSERT @T_EmployeeRoles VALUES(9,  4,          4)
SET NOCOUNT OFF

;WITH CurrentInfo AS
(SELECT
     e.ID AS EmployeeID, e.Name, r.RoleID AS CurrentRoleID, mr.ID AS EmployeeRoleID
     FROM @T_Employee    e
         LEFT OUTER JOIN (SELECT
                              EmployeeID,MAX(ID) AS ID
                              FROM @T_EmployeeRoles
                              GROUP BY EmployeeID
                         ) mr ON e.ID=mr.EmployeeID
         LEFT OUTER JOIN @T_EmployeeRoles r ON mr.ID=r.ID
)
SELECT
    c.EmployeeID AS ID, c.Name, r.RoleName AS "Current Role", llr.RoleName AS "Last Role"
    FROM CurrentInfo c
        LEFT OUTER JOIN @T_Roles r ON c.CurrentRoleID=r.ID
        LEFT OUTER JOIN (SELECT
                             rr.EmployeeID,MAX(rr.ID) AS ID
                             FROM @T_EmployeeRoles           rr
                                 LEFT OUTER JOIN CurrentInfo cc ON rr.ID=cc.EmployeeRoleID
                             WHERE cc.EmployeeRoleID IS NULL
                             GROUP BY rr.EmployeeID
                        ) mr ON c.EmployeeID=mr.EmployeeID
         LEFT OUTER JOIN @T_EmployeeRoles lr ON mr.ID=lr.ID
         LEFT OUTER JOIN @T_Roles llr ON lr.RoleID=llr.ID

OUTPUT:

ID          Name       Current Role    Last Role
----------- ---------- --------------- ---------------
1           John       Manager         Clerk II
2           Jane       Senior Manager  Manager
3           Joe        Manager         NULL

(3 row(s) affected)

When there is only a "Current Role" because the employee has only worked one position, you can push the "Current Role" into the "Last Role" by doing this in the SELECT:

..., ISNULL(llr.RoleName,r.RoleName) AS "Last Role"

EDIT

here is version of the query without the CTE, so it will run on SQL Server 2000, I also replaced the variable tables with the OP's actual table names:

SELECT
    c.EmployeeID AS ID, c.Name, r.RoleName AS "Current Role", llr.RoleName AS "Last Role"
    FROM (SELECT
              e.ID AS EmployeeID, e.Name, r.RoleID AS CurrentRoleID, mr.ID AS EmployeeRoleID
              FROM T_Employee    e
                  LEFT OUTER JOIN (SELECT
                                       EmployeeID,MAX(ID) AS ID
                                       FROM T_EmployeeRoles
                                       GROUP BY EmployeeID
                                  ) mr ON e.ID=mr.EmployeeID
                  LEFT OUTER JOIN T_EmployeeRoles r ON mr.ID=r.ID
         ) c
        LEFT OUTER JOIN T_Roles r ON c.CurrentRoleID=r.ID
        LEFT OUTER JOIN (SELECT
                             rr.EmployeeID,MAX(rr.ID) AS ID
                             FROM T_EmployeeRoles           rr
                                 LEFT OUTER JOIN (SELECT
                                                      e.ID AS EmployeeID, e.Name, r.RoleID AS CurrentRoleID, mr.ID AS EmployeeRoleID
                                                      FROM T_Employee    e
                                                          LEFT OUTER JOIN (SELECT
                                                                               EmployeeID,MAX(ID) AS ID
                                                                               FROM T_EmployeeRoles
                                                                               GROUP BY EmployeeID
                                                                          ) mr ON e.ID=mr.EmployeeID
                                                          LEFT OUTER JOIN T_EmployeeRoles r ON mr.ID=r.ID
                                                 ) cc ON rr.ID=cc.EmployeeRoleID
                             WHERE cc.EmployeeRoleID IS NULL
                             GROUP BY rr.EmployeeID
                        ) mr ON c.EmployeeID=mr.EmployeeID
         LEFT OUTER JOIN T_EmployeeRoles lr ON mr.ID=lr.ID
         LEFT OUTER JOIN T_Roles llr ON lr.RoleID=llr.ID

---

I can't verify this on a live instance but following might just work.

;WITH current AS (
  SELECT  ID = MAX(er.ID)
  FROM    T_Employees er
  GROUP BY
          er.Name
)
SELECT  c.Name, c.Current, l.Last
FROM    (
          SELECT  er.Name, Current = er.Role
          FROM    T_Employees er
                  INNER JOIN current ON current.ID = er.ID
        ) c
        LEFT OUTER JOIN (
          SELECT  er.Name, Last = er.Role
          FROM    T_Employees er
                  INNER JOIN (
                    SELECT ID = MAX(er.ID)
                    FROM   T_Employees er
                    WHERE  NOT EXISTS (
                            SELECT *
                            FROM   current
                            WHERE  current.ID = er.ID
                           )
                    GROUP BY
                           er.Name
                  ) last ON last.ID = er.ID
        ) l ON l.Name = c.Name

---

I think this one will work:

CREATE TABLE #T_EmployeeRole
(
    id int identity(1,1),
    name varchar(10),
    role varchar(20)
)

INSERT INTO #T_EmployeeRole(name, role)
SELECT 'John',  'Clerk I' UNION ALL
SELECT 'John',  'Clerk II' UNION ALL
SELECT 'John',  'Clerk III' UNION ALL
SELECT 'Mark',  'Junior Developer' UNION ALL
SELECT 'Mark', 'Developer' UNION ALL
SELECT 'Mark',  'Senior Developer' UNION ALL
SELECT 'Alice', 'Accountant I' UNION ALL
SELECT 'Alice', 'Treasury Analyst I' UNION ALL
SELECT 'Alice', 'Treasury Analyst II' UNION ALL
SELECT 'Alice', 'Controller' UNION ALL
SELECT 'Chris', 'Secretary I' UNION ALL
SELECT 'Chris', 'Secretary II' UNION ALL
SELECT 'Eric',  'Analyst I' UNION ALL
SELECT 'Eric',  'Analyst II'

select cur.name, cur.role, prev.role
from #T_EmployeeRole cur
left outer join #T_EmployeeRole prev on prev.name = cur.name
    and prev.id = (select max(#T_EmployeeRole.id) from #T_EmployeeRole where #T_EmployeeRole.name = cur.name and #T_EmployeeRole.id <> cur.id)
where cur.id = (select max(#T_EmployeeRole.id) from #T_EmployeeRole where #T_EmployeeRole.name = cur.name)