Group by and show records by criteria MSSQL

Question

I have the following 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 to be able to output each record grouped by PID, and their latest status. If their latest status is terminated, it should be ignored. The output should be like 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, so he isn't shown on the table.

Is there a query for this? or should I just link the table to .net, and loop from there?

What I did that also worked is the ff:

I queried the entire table (with select ) into a datatable ordered by PID then DateHired (asc)
looped for each record in the datatable
and copied the last record detected win the loop for that PID into another datatable as long as the Status != "Terminated"

EDIT Corrected the sample table given

EDIT Included the solution I've tried.

Why was this downvoted? Is there something wrong with the question? — Malcolm Salvador, Jun 19 '14 at 02:56
possible duplicate of [select top 10 records for each category](http://stackoverflow.com/questions/176964/select-top-10-records-for-each-category) — Clockwork-Muse, Jun 19 '14 at 12:14
In my defense, I feel that that question is unclear without sample tables to work with. I gave an example table, and a result of what I wanted from it. — Malcolm Salvador, Jun 20 '14 at 05:37
You should at least have shown what you attempted to get _any_ results. It's good that you gave starting data/expected results, and it was nicely formatted. I agree that sometimes being able to _find_ related questions/answers can be difficult - that's why we're here, to point you in the right direction(s). — Clockwork-Muse, Jun 20 '14 at 07:11
I included the solution I've tried that worked. Please don't close this question, as I feel other users like me can learn from it. I also often look back at the questions that I've asked as reference for other projects. — Malcolm Salvador, Jun 20 '14 at 07:55

Gordon Linoff · Accepted Answer · 2014-06-19T02:30:58.473

Dud has been terminated, but the hire date is afterwards. So, the data you provide and the logic are not consistent. To get the most recent status and to remove those that are terminated, use row_number():

select t.*
from (select t.*, row_number() over (partition by pid order by datehired desc) as seqnum
      from table t
     ) t
where seqnum = 1 and status <> 'Terminated';

row_number() assigns a sequential value starting with 1 to a set of rows. The set of rows is defined by the partition by clause, so in this example, all rows with the same pid are in the same set. The enumeration is then based on the order by clause, so in this case, the row with the largest value of datehired in the set gets a value of 1. The where clause chooses where the value is 1, so that is the largest datehired for each pid.

Oh my I'm sorry! the date is supposed to be 2014-01-30! I'll fix it — Malcolm Salvador, Jun 19 '14 at 02:23

Group by and show records by criteria MSSQL

1 Answers1