SQL Server Query output

Question

I have a table of people records like this:

Tableid PeopleID DeptID StartMonth StartYear
--------------------------------------------
  1       101        1       3        2014
  2       101        2       2        2015
  3       102        2       5        2015
  4       102        5       4        2013
  5       103        6       8        2015
  6       103        7       9        2015

I want only the lastest workhistory for each peopleid with the corresponding tableid, meaning I want to have the following output:

tableid  peopleid  
2        101
3        102
6        103

Here I require only tableid and peopleid in output.

Can you change your start month + date to be a one date field? It's a lot easier to use that in the future — James Z
– James Z, Commented Apr 16, 2015 at 11:01

Gordon Linoff · Accepted Answer · 2015-04-16 11:03:39Z

1

Use row_number():

select t.*
from (select t.*,
             row_number() over (partition by peopleid
                                order by startyear desc, startmonth desc
                               ) as seqnum
      from table t
     ) t
where seqnum = 1;

answered Apr 16, 2015 at 11:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ughai · Accepted Answer · 2015-04-16 11:08:25Z

1

You can use ROW_NUMBER() with PARTITION BY. Something like this.

CREATE TABLE People 
(
    Tableid INT,
    PeopleID INT,
    DeptID INT,
    StartMonth INT,
    StartYear INT
)

INSERT INTO People
    SELECT  1,       101,        1,      3,         2014
    UNION ALL SELECT  2,       101,        2,      2,         2015
    UNION ALL SELECT  3,       102,        2,       5,        2015
    UNION ALL SELECT  4,       102,        5,       4,       2013
    UNION ALL SELECT  5,       103,        6,       8,        2015
    UNION ALL SELECT  6,       103,        7,       9,        2015

;WITH CTE as 
( 
    SELECT Tableid,PeopleID,ROW_NUMBER()OVER(PARTITION BY PeopleID ORDER BY StartYear DESC,StartMonth DESC) r
    FROM People
)
SELECT Tableid, PeopleID FROM CTE
WHERE r = 1

edited Apr 16, 2015 at 11:08

answered Apr 16, 2015 at 11:03

ughai

9,9004 gold badges33 silver badges48 bronze badges

Comments

Mark E · Accepted Answer · 2015-04-16 11:13:30Z

1

Here is a solution that does not use row_number() and will therefore work on versions of SQL Server before 2005:

SELECT Tableid, PeopleID 
FROM People t_outer 
WHERE Tableid = (SELECT TOP 1 Tableid FROM MyTable t_inner WHERE t_inner.PeopleID = t_outer.PeopleID ORDER BY StartYear DESC, StartMonth DESC)

answered Apr 16, 2015 at 11:13

Mark E

7306 silver badges16 bronze badges

Comments

Javaluca · Accepted Answer · 2015-04-16 12:49:59Z

0

SELECT *
FROM table t
WHERE NOT EXISTS ( SELECT 'a'
                   FROM table t2
                   WHERE t2.PeopleID = t.PeopleID 
                   AND t2.StartYear * 100 + t2.StartMonth > t.StartYear * 100 + t.StartMonth
                  )

answered Apr 16, 2015 at 12:49

Javaluca

8571 gold badge6 silver badges13 bronze badges

1 Comment

bob-fornal Over a year ago

You might want to document why this answer is correct.

Collectives™ on Stack Overflow

SQL Server Query output

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related