LAG/LEAD equivalent with grouping (SQL Server 2008 R2) -

note: using sql server 2008 r2 , built in lead/lag functions not available.

i need update table's column contain 'previous' , 'next' values productid - table needs store prevproductid (lag), productid , nextproductid (lead). code below nicely , adapted geri reshef's answer http://blog.sqlauthority.com/2011/11/24/sql-server-solution-to-puzzle-simulate-lead-and-lag-without-using-sql-server-2012-analytic-function/

use adventureworks2008r2 go t1 (   select row_number() over(order salesorderid,productid) n,         s.salesorderid,         s.productid     sales.salesorderdetail s     salesorderid in (43670, 43667, 43663) ) select salesorderid,     case when n%2=1      max(case when n%2=0              productid          end) on (partition n/2)          else max(case when n%2=1              productid          end) on (partition (n+1)/2)      end prevproductid, productid, case when n%2=1      max(case when n%2=0              productid          end) on (partition (n+1)/2)          else max(case              when n%2=1 productid          end) on (partition n/2)      end nextproductid t1 order      t1.salesorderid,     t1.productid 

these results , lag/lead applied rows.

salesorderid prevproductid productid   nextproductid ------------ ------------- ----------- ------------- 43663        null          760         710 43667        760           710         773 43667        710           773         775 43667        773           775         778 43667        775           778         709 43670        778           709         710 43670        709           710         773 43670        710           773         776 43670        773           776         null 

what need group lag/lead values salesorderid first occurrence pointing lag of null , last occurrence pointing lead of null within salesorderid group.

salesorderid prevproductid productid   nextproductid ------------ ------------- ----------- ------------- 43663        null          760         null 43667        null          710         773 43667        710           773         775 43667        773           775         778 43667        775           778         null 43670        null          709         710 43670        709           710         773 43670        710           773         776 43670        773           776         null 

thanks in advance suggestions.

you can adding salesorderid each of partition by clauses in query (row_number() over(), 4 x max() over()):

with t1 (   select  salesorderid,              productid,              n = row_number() over(partition salesorderid order salesorderid, productid)        (values                  (43663, 760), (43667, 710), (43667, 773), (43667, 775), (43667, 778),                  (43670, 709), (43670, 710), (43670, 773), (43670,  776),                 (43680,  1), (43680,  2), (43680,  3), (43680,  4), (43680,  5),                 (43680,  6), (43680,  7), (43680,  8), (43680,  9), (43680,  10),                 (43681,  1), (43681,  2), (43681,  3), (43681,  4), (43681,  5),                 (43681,  6), (43681,  7), (43681,  8), (43681,  9), (43681,  10)             ) x (salesorderid, productid) ) select  t1.salesorderid,         prevproductid = case when n % 2 = 1                             max(case when n % 2 = 0 t1.productid end)                                     over(partition t1.salesorderid, n / 2)                             else                                  max(case when n % 2 = 1 productid end)                                     over(partition t1.salesorderid, (n + 1) / 2)                         end,         t1.productid,         nextproductid = case when n % 2 = 1                             max(case when n % 2 = 0 t1.productid end)                                     over(partition t1.salesorderid, (n + 1) / 2)                             else                                  max(case when n % 2 = 1 productid end)                                     over(partition t1.salesorderid, n / 2)                         end    t1; 

which (excluding additional rows have added further testing) yields:

salesorderid    prevproductid   productid   nextproductid --------------------------------------------------------------- 43663           null            760             null 43667           null            710             773 43667           710             773             775 43667           773             775             778 43667           775             778             null 43670           null            709             710 43670           709             710             773 43670           710             773             776 43670           773             776             null