How do I display only the last 5 transactions?

Question

I am using a script that converts SQL to Fox Pro (Visual Fox Pro and I generated code to show me a payment history. however I only want it to show the last 5 payments with that corresponding payments

This query will show all payments ever made. I only need the last 5 payments

trns.trxdate = payment date
trns.payment = payment amount

'Trying to generate SIF/PIF report for Feb 2024
strsql = "SELECT dbt.ourfile, dbt.ncustfile, dbt.preourfile, dbt.assdate, dbt.sdate, dbt.jdate, debt.cdate, trns.trxdate, trns.payment FROM dbt JOIN trns ON dbt.ourfile = trns.ourfile where dbt.custid in ('C1495', 'C1196') AND dbt.cdate => {d'2024-02-01'} AND trns.trx in ('01', '02', '07', '09')"

The code above produces the following results, I am looking for a way to only show the most recent 5 transactions. Some accounts have hundreds of transactions.

![1]: https://imgur.com/a/Hz8U388.jpg "results of data pull"

My desired result is this (only showing the last 5 transactions)

![1]: https://imgur.com/P2QAC1D.jpg "desired results"

Any help would be appreciated. Google was not my friend when trying to find a solution.

I have tried to use grouping with MAX and CTE. I kept getting compilation errors when I executed the script.

Answer 1

You could select the TOP 5 along with an ORDER BY DESCENDING. That should give you the most recent 5 transactions in descending order. If you need those 5 records in ascending order, you could do another query to reorder the results.

SELECT TOP 5 dbt.ourfile, dbt.ncustfile, dbt.preourfile, ;
    dbt.assdate, dbt.sdate, dbt.jdate, debt.cdate, ;
    trns.trxdate, trns.payment ;
    FROM dbt ;
    JOIN trns ;
    ON dbt.ourfile = trns.ourfile ;
    WHERE dbt.custid IN ('C1495', 'C1196') ;
    AND dbt.cdate => {d'2024-02-01'} ;
    AND trns.trx IN ('01', '02', '07', '09') ;
    ORDER BY trns.trxdate DESC