SHOW:
|
|
- or go back to the newest paste.
| 1 | CREATE TABLE IF NOT EXISTS `payments` ( | |
| 2 | `uid` int(11) NOT NULL, | |
| 3 | `amount` float NOT NULL, | |
| 4 | `pay_date` datetime NOT NULL | |
| 5 | ) ENGINE=MyISAM DEFAULT CHARSET=latin1; | |
| 6 | ||
| 7 | -- | |
| 8 | -- Дамп данных таблицы `payments` | |
| 9 | -- | |
| 10 | ||
| 11 | INSERT INTO `payments` (`uid`, `amount`, `pay_date`) VALUES | |
| 12 | (1, 1000, '2012-08-01 12:07:10'), | |
| 13 | (1, 1500, '2012-08-01 10:29:29'), | |
| 14 | (2, 100, '2012-08-01 00:00:00'), | |
| 15 | - | (2, 50, '2012-08-01 09:15:24'); |
| 15 | + | (2, 50, '2012-08-01 09:15:24'); |
| 16 | ||
| 17 | ||
| 18 | ||
| 19 | SELECT p.uid, p.amount | |
| 20 | FROM payments p | |
| 21 | JOIN | |
| 22 | (SELECT uid, MAX(pay_date) AS max_dt | |
| 23 | FROM payments | |
| 24 | GROUP BY uid) sel ON p.uid = sel.uid AND p.pay_date = sel.max_dt | |
| 25 | ||
| 26 | ||
| 27 | ||
| 28 | uid | amount | |
| 29 | 1 | 1000 | |
| 30 | 2 | 50 |
