Top predicate 1

[dysan]

I have an sql data base that I connect to via MS Access.

Table has customer transactions...Each transaction has a unique id

Using the TOP predicate I can get the 2 most recent transactions in the table. The problem is that
I wish to return the last 2 transactions for every customer.

Any suggestions oh how I could go about doing this would be greatly appreciated.

Thanks

 

[dhookom]

Try something like:

SELECT *
FROM tblCustTransactions
WHERE ID IN 
(SELECT TOP 2 ID
 FROM tblCustTransactions T
 WHERE T.CustomerID = tblCustTransactions.CustomerID
 ORDER BY T.TransDate DESC, T.ID DESC)

Duane
Hook'D on Access
MS Access MVP

 

[dysan]

SELECT dbo_V_EN_PERFREP.*
FROM dbo_V_EN_PERFREP
WHERE dbo_V_EN_PERFREP.Del_Date IN
SELECT TOP 2 Del_Date
FROM dbo_V_EN_PERFREP
WHERE dbo_V_EN_PERFREP.Account = dbo_V_EN_PERFREP.Account
ORDER BY dbo_V_EN_PERFREP.Del_Date DESC, dbo_V_EN_PERFREP.Ticket DESC)

Here's what I come up with... Any way to improve upon the speed of the query itself?

 

[PHV]

SELECT A.Account, A.Del_Date, A.Ticket
FROM dbo_V_EN_PERFREP AS A INNER JOIN dbo_V_EN_PERFREP AS B ON A.Account = B.Account AND A.Del_Date <= B.Del_Date
GROUP BY A.Account, A.Del_Date, A.Ticket
HAVING Count(*) <= 2
ORDER BY 1, 2 DESC;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886