How to write a SQL query to get the last transaction for each member?

advertisements

My query is like this:

SELECT
    FOB_Id, MM_Id, CONVERT(varchar(25),FOB_Date,103), FOB_Balance, FOB_B_CR_DR
FROM
    FO_Opening_Balance
WHERE
    MM_Id IN (1,597)
        AND month(FOB_Date)=1
        AND YEAR(fob_date) = 2014
GROUP BY
    MM_Id, FOB_Id, FOB_Date, FOB_Balance, FOB_B_CR_DR
ORDER BY
    FOB_Date desc, FOB_Id DESC

It will display as follows:

FOB_Id  | MM_Id | FOB_Date  |  Balance | cr/dr
-------------------------------------------------
625773  |  1    |31/01/2014 |   247.32 | Cr
624391  | 597   |31/01/2014 |  1663.42 | Dr
623999  |  1    |31/01/2014 |   447.32 | Cr
622000  | 597   |31/01/2014 |  1551.42 | Dr
621877  |  1    |31/01/2014 |   559.32 | Cr
621473  |  1    |31/01/2014 |   865.32 | Cr
620907  |  1    |24/01/2014 |   893.32 | Cr
615844  | 597   |06/01/2014 |  1439.42 | Dr

But I want to display only last transaction of member id, if I give top 1 at select statement it will display only one row, here I given two member id 1 and 597, it should display only first two rows of above table, like that if give many member id's, it should display only that member transaction, kindly help.


I am assuming you are using SQL Server, based on the query syntax. If you want the last transaction, you can use row_number():

select FOB_Id,  MM_Id, dte, FOB_Balance, FOB_B_CR_DR
from (select FOB_Id,  MM_Id , convert(varchar(25), FOB_Date,103) as dte, FOB_Balance, FOB_B_CR_DR, FOB_Date,
             row_number() over (partition by mm_id order by fob_date desc) as seqnum
      from FO_Opening_Balance
      where MM_Id in (1,597) and
            month(FOB_Date) = 1 and YEAR(fob_date) = 2014
     ) t
where seqnum = 1
order by FOB_Date desc, FOB_Id desc;