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

  • nrdroque

    Old Hand

    Points: 344

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



  • DesNorton


    Points: 22714

    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)
    SELECT *
    FROM cteData
    where rn = 1;
  • nrdroque

    Old Hand

    Points: 344

    Thanks i will walk from there

  • Chris Harshman


    Points: 41760

    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 4 (of 4 total)

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