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

  • nrdroque

    Old Hand

    Points: 333

    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

     

  • DesNorton

    SSC-Insane

    Points: 22542

    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;
  • nrdroque

    Old Hand

    Points: 333

    Thanks i will walk from there

  • Chris Harshman

    SSC-Forever

    Points: 41661

    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