sql - How do I grab the most recent price change from table -

i have sql table audit trail. trying grab recent time price changed.

using query have following data

select    id id_1           ,item_no, price           ,post_dt           ,post_tm           ,aud_action       iminvaud_sql      item_no = '1-advent english'            , aud_action in ('b','c')  order     id desc  id_1    item_no                        price    post_dt                  post_tm              aud_action  221 1-advent english                2.000000    2014-08-18 00:00:00.000 1900-01-01 10:19:35.113 c 218 1-advent english                2.000000    2014-08-18 00:00:00.000 1900-01-01 10:19:35.110 b 217 1-advent english                2.000000    2014-08-18 00:00:00.000 1900-01-01 10:01:47.163     c 216 1-advent english                3.000000    2014-08-18 00:00:00.000 1900-01-01 10:01:46.757     b 59  1-advent english                3.000000    2013-08-19 00:00:00.000 1900-01-01 13:23:32.950     c 58  1-advent english                1.000000    2013-08-19 00:00:00.000 1900-01-01 13:23:32.890     b 

the system writes b before change , c change in sense b , c records grouped. example wanted 217 record because recent price change.

your query essentially:

select s.* iminvaud_sql s s.item_no = '1-advent english' , s.aud_action in ('b','c')  order id desc; 

as far can tell, "b" , "c" not adding information. instead, let's @ first occurrence of recent price. base on id. following work if prices monotonic (either increasing or decreasing):

select top 1 s.* iminvaud_sql s exists (select 1               iminvaud_sql s2               s2.item_no = s.item_no , s2.aud_action in ('b','c') , s2.price = s.price              ) ,       s.aud_action in ('b','c') , s.item_no = '1-advent english' order s.id_1 asc; 

if isn't case, can use trick. trick take difference between row_number() , row_number() partitioned price. largest values difference recent price.

select top 1 s.* (select s.*,              (row_number() on (order id_1) -               row_number() on (partition price order id_1)              ) pricegroup       iminvaud_sql s       s2.aud_action in ('b','c') , s.item_no = '1-advent english'      ) s order price_group, s.id_1 asc;