CODEWITHSUNDEEP
mysqlsql
James Wilson
James Wilson

Reputation: 809

How can I write an SQL query to show the latest transaction in one table?

I have 2 DB tables that both share an Order Number column.

One table is "orders" and the Order Number is the unique key.

The second table is my "transactions" table that has one row, per transaction made for each order number. Based on the fact we take monthly payments, the "transactions" table obviously has multiple rows with a unique date but many repeats of a each Order Number.

How can I run a query which has a list of unique OrderNumbers in one column, and the latest "TransDate" (Transaction Date) in the second column.

I tried the below but its pulling back the first TransDate that exists for each ordernumber, not the latest one. I think I need a sub query of some sort:

select orders.ordernumber, transdate from orders 
join transactions on transactions.ordernumber = orders.ordernumber
where status = 'booking'
group by ordernumber
order by orders.ordernumber, TransDate DESC

Upvotes: 0

Views: 1696

Answers (3)

NETRookie
NETRookie

Reputation: 86

Change the order by line to 

order by Transdate DESC, orders.ordernumber

Here's the full query with the change

select orders.ordernumber, transdate from orders 
join transactions on transactions.ordernumber = orders.ordernumber
where status = 'booking'
group by ordernumber
order by Transdate DESC, orders.ordernumber

Upvotes: 0

Barranka
Barranka

Reputation: 21047

Use aggregate functions, specifically max():

select o.ordernumber, max(transdate) as last_transdate
from orders as o
     inner join transactions as t on o.ordernumber = t.ordernumber
-- where conditions go here
group by ordernumber

If you need to pull the details of the last transaction for each order, you can use the above query as a data source of another query and join it with the transactions table:

select a.*, t.*
from (
        select o.ordernumber, max(transdate) as last_transdate
        from orders as o
             inner join transactions as t on o.ordernumber = t.ordernumber
        -- where conditions go here
        group by ordernumber
    ) as a
inner join transactions as t on a.ordernumber = t.ordernumber and a.last_transdate = t.transdate

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71384

You should just use MAX() function along with grouping on order number. There also doesn't seem to be any reason to do a join here.

SELECT
    ordernumber,
    MAX(transdate) AS maxtransdate
FROM transactions
WHERE status = 'booking'
GROUP BY ordernumber
ORDER BY ordernumber ASC

Upvotes: 3

Related Questions