need implement condtion in table

Hey Raja_23 

You could do this:

select
a.*
from vwShipmentStatus a
join (
select [ShipmentKey],max([ShipmentEventNumber]) as MaxShipmentEvent_NR
from vwShipmentStatus 
GROUP BY [ShipmentKey] ) b on a.[ShipmentKey] = b.[ShipmentKey] and a.[ShipmentEventNumber] = b.MaxShipmentEvent_NR

which will give you the result you are looking for (e.g. expected output).

If you want more flexibility you could also do 

select
a.*, 
b.MaxShipmentEvent_NR,
case 
when a.[ShipmentEventNumber] = b.MaxShipmentEvent_NR then 'Yes'
else 'No' 
end as Max_event
from vwShipmentStatus a
join (
select [ShipmentKey],max([ShipmentEventNumber]) as MaxShipmentEvent_NR
from vwShipmentStatus 
GROUP BY [ShipmentKey] ) b on a.[ShipmentKey] = b.[ShipmentKey] 

This will give you the total table with a flag (Max_event). You can filter on this to only show max_event or to show everything

Hamza