Query to return last row for each unique value on a column

• nrdroque

  Old Hand

  Points: 348

  More actions

  July 11, 2019 at 4:01 pm

  #3662021

  Hi there,

  is it possible to get one line for each unique value (last one inserted)

  

  just show me the way dont give me the code please.

  thanks

   

• DesNorton

  SSC-Insane

  Points: 24530

  More actions

  July 11, 2019 at 4:34 pm

  #3662029

  Take a look at the ROW_NUMBER() function

  WITH cteData AS (
    SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY {Fields to group by} ORDER BY {Fields that identify the order} DESC)
    FROM 
  )
  SELECT *
  FROM cteData
  where rn = 1;

  ---

  How to post data/code on a forum to get the best help.

• nrdroque

  Old Hand

  Points: 348

  More actions

  July 11, 2019 at 5:42 pm

  #3662055

  Thanks i will walk from there

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  July 11, 2019 at 6:04 pm

  #3662060

  There is another common pattern to solving this question, to use a CROSS APPLY, e.g.

  ...
  FROM Items i
    CROSS APPLY (SELECT TOP 1 statuscode FROM ItemStatus s WHERE s.ItemCode = i.ItemCode ORDER BY StatusDate DESC)

  Which one works better will depend on your data, such as how many rows per Equipamento code you have in your table, and if there is an index on your table that matches the conditions you are grouping and sorting by.