help with query

• aviv.sages

  Grasshopper

  Points: 13

  More actions

  July 13, 2013 at 1:39 pm

  #277414

  hi,

  I have a table:

  version item price

  1 a 1.00

  2 a 1.20

  3 a 1.30

  1 b 2.00

  2 b 2.20

  I need to create a query that will select the latest version price for each item

  the outcome should be like that:

  version item price

  3 a 1.30

  2 b 2.20

  please help 🙂

  regards,

  aviv

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  July 13, 2013 at 2:36 pm

  #1632194

  Yopu can user ROW_NUMBER() to do this - something like this:

  select *

  from

  (

  select

  row_number() over (partition by item order by version desc) as xrow,

  version,

  item,

  price

  from table

  )

  where xrow = 1

  This gives you the latest version for each item because the version numbers are in desc order