sql - Find the last entry for specific user id -

i trying find last entry specific user each location. need ignore rows balance zero.

sample table:

    id location user balance     -- -------- ---- -------      1 gla         10.21      2 gla         0.00      3 gla      b    45.68      4 gla      b    12.56      5 edi      c    1.23      6 edi      d    5.48      7 edi      d    0.00      8 lon      e    78.59      9 man      f    0.00     10 man      f    12.98     11 man      g    56.45     12 man      g    89.25     13 man      h    0.00     14 bir         0.00     15 bir         32.87 

result required:

    id location user balance     -- -------- ---- -------      4 gla      b    12.56      5 edi      c    1.23      8 lon      e    78.59     10 man      f    12.98     12 man      g    89.25     15 bir         32.87 

i have been left cover colleague off sick , unfotunately sql knowledge limited in comparison.

i have been researching using max , partition no luck far. sql server 2005 database.

any assistance appreciated. add progress make.

many thanks.

you should able use row_number() result:

select id, location, [user], balance  (   select id, location, [user], balance,     row_number() over(partition location, [user]                         order id desc) seq   yourtable ) d seq = 1   , balance <> 0 order id; 

see sql fiddle demo