Select latest status with condition... help please 1

[peac3]

Hello,

I have number of network status that comes sequentially per network ID order by time as per below data

network_id	Status_code	status_time
1	        sent	        6/12/2015 15:58:25
1	        delivered	6/12/2015 15:59:49
2	        undelivered	6/12/2015 15:59:50
2	        sent	        6/12/2015 15:59:51
3	        sent	        6/12/2015 15:59:52

I need to display only the latest status per network_id with the time associate with it.
however if per network_id there is status undelivered, i would like to the undelivered to be displayed regardless the rest of the status.

so the result as per below.

network_id	Status_code	status_time
1	        delivered	6/12/2015 15:59:49
2	        undelivered	6/12/2015 15:59:50
3	        sent	        6/12/2015 15:59:52

It seems simple, but it took me quite so long...

Appreciate for the help

 

[djj55]

May not be the best way but...

WITH cte AS (
SELECT Network_id, Status_Code, Status_Time, ROW_COUNT() OVER(PARTITION BY Network_id ORDER BY Status_Time DESC) AS rcnt
)
SELECT Network_id, Status_Code, Status_Time 
FROM cte 
WHERE rcnt = 1;

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[DaveInIowa]

You need to give more weight to the 'undelivered' entries before their date/time.

;WITH cte AS (
SELECT *,
	   ROW_NUMBER() OVER (PARTITION BY network_id ORDER BY CASE Status_code WHEN 'undelivered' THEN 0 ELSE 1 END, status_time DESC) rowno
  FROM @NetworkData
)

SELECT *
  FROM cte
 WHERE rowno = 1

 

[peac3]

Thanks DaveInIowa, brilliant!