sql - Group by and show records by criteria MSSQL -

sql - Group by and show records by criteria MSSQL -

i have next table :

| pid | fullname | position | salary | status | datehired | 11 | dave | clerk | 100 | extended | 2014-01-30 | 11 | dave | clerk | 100 | hired | 2014-01-02 | 22 | chris | guard | 80 | extended | 2014-01-30 | 22 | chris | dj | 100 | hired | 2014-01-02 | 33 | dud | clerk | 200 | terminated| 2014-01-30 | 33 | dud | clerk | 200 | hired | 2014-01-03 | 44 | trish | clerk | 200 | hired | 2014-01-25

i need able output each record grouped pid, , latest status. if latest status terminated, should ignored. output should this:

| pid | fullname | position | salary | status | datehired | 11 | dave | clerk | 100 | extended | 2014-01-30 | 22 | chris | guard | 80 | extended | 2014-01-30 | 44 | trish | clerk | 200 | hired | 2014-01-25

dud has been terminated, isn't shown on table.

is there query this? or should link table .net, , loop there?

what did worked ff:

i queried entire table (with select ) datatable ordered pid datehired (asc) looped each record in datatable and copied lastly record detected win loop pid datatable long status != "terminated"

edit corrected sample table given

edit included solution i've tried.

dud has been terminated, hire date afterwards. so, info provide , logic not consistent. recent status , remove terminated, utilize row_number():

select t.* (select t.*, row_number() on (partition pid order datehired desc) seqnum table t ) t seqnum = 1 , status <> 'terminated';

row_number() assigns sequential value starting 1 set of rows. set of rows defined partition by clause, in example, rows same pid in same set. enumeration based on order by clause, in case, row largest value of datehired in set gets value of 1. where clause chooses value 1, largest datehired each pid.

sql sql-server grouping