Select Count with trimmed rows

Question

I have a MS SQL DB table, where I store part status journal, ie. how a HW part has changed somewhere.

I need to get exactly one row (ordered by date) for every part and then count all rows, where new_status is equal to 2.

Now to put it in example, I came up with this query:

Select part_sys_id, old_status, new_status, sys_created 
From Vhw_status_journal 
Where i = 5 
Order by sys_created desc

Which gets me (note that i is just an internal column):

part_sys_id | old_status | new_status | sys_created
-----------------------------------------------------------
     21     |     2      |     3      | 2015-08-19 11:00:25
     21     |    NULL    |     2      | 2015-08-19 10:59:28
     20     |     1      |     2      | 2015-08-18 14:13:04
     20     |     2      |     1      | 2015-08-17 10:51:03
     20     |    NULL    |     2      | 2015-08-12 15:05:46

Now it turns out that I am completely lost when I have to get only the newest entry for each part_sys_id (I tried Select Disctint to no avail) and then even count the output rows where new_status = 2.

My requested output is:

part_sys_id | old_status | new_status | sys_created
-----------------------------------------------------------
     21     |     2      |     3      | 2015-08-19 11:00:25
     20     |     1      |     2      | 2015-08-18 14:13:04

And then I need to count the rows with new_status = 2, ie. I should then get something like:

count
-----
  1

Answer 1

try this

 with cte
    as
    (
    select row_number() over(partition by part_sys_id order by sys_created desc) as ri,part_sys_id, old_status, new_status, sys_created
    from Vhw_status_journal
    where i = 5
    )

    select count(*) from cte where ri=1 and new_status=2