Assi9stance in a Sql statement

Question

Hi allI have a Table and I want to retrieve the record with the latest Sequence for each code.&nbsp; I need some help to write a query.&nbsp; Thanks to help me out.&nbsp;Code&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sequence&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TitleJ001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; John&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Junior AdministratorJ001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; John&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AdministratorJ001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; John&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Senior Administrator&nbsp;S001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sue&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AccountantS001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sue&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Senior Accountant&nbsp;R001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Robert&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Manager&nbsp;My desired outputJ001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; John&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Senior AdministratorS001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sue&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Senior AccountantR001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Robert&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Manager&nbsp;

olafhelper · Answer

Jlallmahomed&nbsp;, 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)
SELECT T.*
FROM yourTable AS T
     INNER JOIN
     cte
         ON T.Code = cte.Code
            AND t.Sequence = cte.MaxSequence

Forum Discussion

Assi9stance in a Sql statement

Share

Resources