Post reply

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

  • July 11, 2019 at 4:01 pm

    #3662021

    Hi there,

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

    111

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

    thanks

     

  • 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.

  • July 11, 2019 at 5:42 pm

    #3662055

    Thanks i will walk from there

  • 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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply