[MySQL] How to get the latest items distinctively in a row?

Question

Hi,

I want to get the remaining/latest balance of the cardnumber from the rows. Below is the sample of the table.

  trans_id |     cardnumber   |      trans_date     |  balance
---------------------------------------------------------------
1          | 1000005240000008 | 2009-07-03 04:54:27 |      88
2          | 1000005120000008 | 2009-07-04 05:00:07 |       2
3          | 1000005110000008 | 2009-07-05 13:18:39 |       3
4          | 1000005110000008 | 2009-07-06 13:18:39 |       4
5          | 1000005110000008 | 2009-07-07 14:25:32 |       4.5
6          | 1000005120000002 | 2009-07-08 16:50:51 |      -1
7          | 1000005240000002 | 2009-07-09 17:03:17 |       1

The result should look like this:

  trans_id |     cardnumber   |      trans_date     |  balance
---------------------------------------------------------------
1          | 1000005110000008 | 2009-07-07 14:25:32 |       4.5
2          | 1000005120000002 | 2009-07-08 16:50:51 |      -1
3          | 1000005240000002 | 2009-07-09 17:03:17 |       1

I already have a query but it goes something like this:

SELECT cardnumber, MAX(balance), trans_date
FROM transactions
GROUP BY cardnumber

I really need help on this, im having a hard time. :( Thanks in advance.

Mark

Answer 1

I don't have a MySQL in front of me at the moment, but something like this should work:

SELECT latest.cardnumber, latest.max_trans_date, t2.balance
  FROM
    (
      SELECT t1.cardnumber, MAX(t1.trans_date) AS max_trans_date
      FROM transactions t1
      GROUP BY t1.cardnumber
    ) latest
    JOIN transactions t2 ON (
      latest.cardnumber = t2.cardnumber AND 
      latest.max_trans_date = t2.trans_date
    )

Probably requires 5.0.x or later. There may be a better way. It's 3AM :-D

Answer 2

Almost the same as derobert's, but other way around. The idea is anyway that you make a subquery that takes the cardnumber with the latest (max) transaction date and then join that with the original table. This of course assumes that there aren't any transactions on cardnumber occuring at the exact same time.

SELECT t1.trans_id, t1.cardnumber, t1.trans_date, t1.balance
FROM transaction AS t1
JOIN (SELECT MAX(trans_date), cardnumber FROM transactions) AS t2 ON t2.cardnumber = t1.cardnumber

Answer 3

SELECT * FROM transactions WHERE (cardnumber,trans_date) in (SELECT cardnumber, MAX(trans_date) FROM transactions GROUP BY cardnumber);