How to Make a SQL Query for Last Transaction of Every Account

How to make a SQL query for last transaction of every account?

This is an example of a greatest-n-per-group query. This question comes up several times per week on StackOverflow. In addition to the subquery solutions given by other folks, here's my preferred solution, which uses no subquery, GROUP BY, or CTE:

SELECT t1.*
FROM transactions t1
LEFT OUTER JOIN transactions t2
  ON (t1.acct_id = t2.acct_id AND t1.trans_date < t2.trans_date)
WHERE t2.acct_id IS NULL;

In other words, return a row such that no other row exists with the same acct_id and a greater trans_date.

This solution assumes that trans_date is unique for a given account, otherwise ties may occur and the query will return all tied rows. But this is true for all the solutions given by other folks too.

I prefer this solution because I most often work on MySQL, which doesn't optimize GROUP BY very well. So this outer join solution usually proves to be better for performance.

How to make SQL query to retrieve all last transactions for every year from a transaction table

Try breaking your TransactionDate into a year using DATEPART. Once you have that, you can select max TransactionDate and group by year, to get the last transaction of each year

That would look like this…

SELECT LastTransactionDateOfYear
FROM (
    SELECT DATEPART(year,TransactionDate),
        MAX(TransactionDate) LastTransactionDateOfYear
    FROM <yourtable>
    GROUP BY DATEPART(year,TransactionDate)
)

How can I get the last transaction from a SQL table for each Item that appears in the table?

Seems like you could do this with ROW_NUMBER and a windowed COUNT to help the filtering:

WITH CTE AS(
    SELECT Item,
           TransID,
           DateofTransaction,
           ROW_NUMBER() OVER (PARTITION BY Item ORDER BY DateofTransaction DESC) AS RN,
           COUNT(CASE WHEN TransID NOT IN ('A','D') THEN 1 END) OVER  (PARTITION BY Item) AS NotAD --REad that it must have at least one transaction that isn't A or D
    FROM dbo.YourTable)
SELECT Item,
       TransID,
       DateofTransaction
FROM CTE
WHERE RN = 1
  AND NotAD > 0;

I want to find the last transaction for each account prior to a certain date

You could use the row_number windows function to number the rows per account id, and take the last one per account:

SELECT account_id, [date], balance
FROM   (SELECT account_id, [date], balance,
               ROW_NUMBER() OVER (PARTITION BY account_id
                                  ORDER BY [date] DESC, id DESC) AS rn
        FROM   [transaction]) t
WHERE  rn = 1

How to get the last transaction of each player - SQL Solution

select * from table 
where id in (select MAX(id) from table group by account_id)

SQL First and Last Transaction Report

I would try and pull out the customer info you need to filter out the rest of the data. I would start with a CTE to get the MIN and MAX dates for each customer. I would then JOIN that back up to the original table and just get the records for the Customer that are on that MAX date.

I would assume you have something that is more Unique then the customer name, and if so I would use that instead. But here is what I came up with given the data you provided:

CREATE TABLE #tmp(SerialNumber int, SearialID int,CustomerName varchar(100), BankAccount int, 
                    TranDate date, Price decimal(10,2),TracerID int,Store varchar(20))

INSERT INTO #tmp VALUES
(2,2,'Peter Smith',14564,'1/1/2021',10,756,'Kroger'),
(1,1,'John Do',12345,'1/1/2021',10,156,'Walmart'),
(1,1,'John Do',12345,'1/15/2021',5,148,'Walmart'),
(1,1,'John Do',12345,'1/15/2021',15,148,'Walmart'),
(2,2,'Peter Smith',14564,'1/12/2021',12,756,'Kroger')

;WITH CTE AS
    (
    SELECT Min(TranDate) FirstDate, Max(TranDate) LastDate, CustomerName
    FROM #tmp
    GROUP BY CustomerName
    )
SELECT T.SerialNumber,T.SearialID,T.CustomerName,t.BankAccount,C.FirstDate FirstTrans, C.LastDate,SUM(t.Price) Price,T.Store 
    FROM #tmp T
    INNER JOIN CTE C on t.CustomerName = c.CustomerName and t.TranDate = c.LastDate
GROUP BY T.SerialNumber,T.SearialID,T.CustomerName,t.BankAccount,C.FirstDate,C.LastDate,T.Store 

---

Related Topics