CODEWITHSUNDEEP
sql
kakarot
kakarot

Reputation: 45

SQL- How to get last record for each account

I have two tables Member and Discount. I am looking to retrieve last recordnumber row for each account. Is there a way to do this?

Sample data from Discount table

enter image description here

Sample Data Member table

enter image description here

enter code here

Desired output

enter code here

enter image description here

enter code here

SQL query that I tried but it doesn't return the desired result:

SELECT 
    Max(a.recordnumber)
    , a.account
    , d.NAME
    , d.memberid
    , a.effectivedate
    , a.amount 
 FROM 
    member m 
    LEFT JOIN discount d ON m.account on d.account;

Upvotes: 1

Views: 76

Answers (2)

Casey Crookston
Casey Crookston

Reputation: 13955

If I am understanding your question, I think something like this might do it:

SELECT TOP 1 * FROM (
    SELECT 
        a.recordnumber
        , a.account
        , d.NAME
        , d.memberid
        , a.effectivedate
        , a.amount 
     FROM 
        member m 
        LEFT JOIN discount d ON m.account on d.account
     ORDER BY a.recordnumber
) inner

That will get you only one record. If you end up with the first record instead of the last, then change

ORDER BY a.recordnumber

to:

ORDER BY a.recordnumber DESC

Upvotes: 1

forpas
forpas

Reputation: 164139

You can join the table member to a query that returns the last row of each account in table discount:

select d.recordnum, m.account, m.name, m.memberid, d.effectivedate, d.amount
from member m left join (
  select d.* from discount d
  where not exists (
    select 1 from discount
    where account = d.account and recordnum > d.recordnum
  )
) d on d.account = m.account

Upvotes: 1

Related Questions