Assi9stance in a Sql statement

Copper Contributor

Hi all

I have a Table and I want to retrieve the record with the latest Sequence for each code.  I need some help to write a query.  Thanks to help me out.


Code          Name          Sequence         Title

J001            John            1                       Junior Administrator

J001            John            2                        Administrator

J001            John            3                       Senior Administrator


S001           Sue               1                        Accountant

S001           Sue               2                        Senior Accountant


R001           Robert          1                       Manager


My desired output

J001            John            3                       Senior Administrator

S001            Sue             2                       Senior Accountant

R001           Robert         1                       Manager


1 Reply

@Jlallmahomed , an easy one, aggregate on "Code" to get the MAX = highest sequence.

;WITH cte AS
    (SELECT Code, MAX(Sequence) AS MaxSequence
     FROM yourTable
     GROUP BY Code)
FROM yourTable AS T
         ON T.Code = cte.Code
            AND t.Sequence = cte.MaxSequence