How to find the last record in a sequence of entries

[jjk238]

Hello,

I have a table that has up to 5 entries of the same Application number, with the Status changing on each one. What I'd like to do is get the highest number (last in the sequence) for each application number. Here's what the table looks like:

App Num Sequence Status EntryDate Product
1321 1 12 10/3/09 HELOC
1321 2 8 10/3/09 HELOC
1321 3 32 10/3/09 HELOC
1322 1 33 10/4/09 HEIL
1323 1 12 10/4/09 PLOC
1323 2 32 10/4/09 PLOC

Here's what I want to retrieve:

App Num Sequence Status EntryDate Product
1322 1 33 10/4/09 HEIL
1323 2 32 10/4/09 PLOC
1321 3 32 10/3/09 HELOC

Any idea how to do this?

 

[r937]

SELECT t.AppNum     
     , t.Sequence    
     , t.Status    
     , t.EntryDate     
     , t.Product     
  FROM ( SELECT AppNum     
              , MAX(Sequence) AS last_seq
           FROM daTable
         GROUP
             BY AppNum ) AS m
INNER
  JOIN daTable AS t
    ON t.AppNum = m.AppNum
   AND t.Sequence = m.last_seq

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

Please see this blog

http://blogs.lessthandot.com/index....rver/including-an-aggregated-column-s-related

which explorers multiple ways of solving this problem.