CODEWITHSUNDEEP
sqlsql-server
Sandip
Sandip

Reputation: 49

SQL Server Query output

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.

Upvotes: 1

Views: 82

Answers (4)

Javaluca
Javaluca

Reputation: 857

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
                  )

Upvotes: 0

Mark E
Mark E

Reputation: 730

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)

Upvotes: 1

ughai
ughai

Reputation: 9880

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269553

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;

Upvotes: 1

Related Questions