My Objective is to grab the last few rows (I'm trying three) from a table for each account. I have no trouble getting the first rows but I'm having trouble making the top three most recent rows. For example, let's say I have the following table:
+--------+------------+------------+-----------+
| rownum | entryDate | particular | accountID |
+--------+------------+------------+-----------+
| 1 | 2015-10-01 | Item1 | 1 |
| 2 | 2015-10-01 | Item2 | 1 |
| 3 | 2015-10-02 | Item3 | 1 |
| 4 | 2015-10-02 | Item4 | 1 |
| 5 | 2015-10-02 | Item5 | 2 |
| 6 | 2015-10-03 | Item6 | 2 |
| 7 | 2015-10-05 | Item7 | 3 |
+--------+------------+------------+-----------+
What I'm trying to get is:
+--------+------------+------------+-----------+
| rownum | entryDate | particular | accountID |
+--------+------------+------------+-----------+
| 4 | 2015-10-02 | Item4 | 1 |
| 3 | 2015-10-02 | Item3 | 1 |
| 2 | 2015-10-01 | Item2 | 1 |
| 6 | 2015-10-03 | Item6 | 2 |
| 5 | 2015-10-02 | Item5 | 2 |
| 7 | 2015-10-05 | Item7 | 3 |
+--------+------------+------------+-----------+
Where Item1
was removed because it already had 3 before it.
I've tried the following code, but it doesn't retrieve the most recent
select rownum, entryDate, particular, accountID
from (
select entryDate, particular, accountID
@rownum := if(@account = accountID, @rownum + 1, 1) rownum,
@account := accountID
from entries
join ( select @rownum := 0, @account := 0 ) init
order by accountID, entryDate desc) t
where t.rownum <= 3 -- Limits the rows per account
If anyone could point me in the right direction that'd be great!
EDIT: However, what I'm retrieving as a result is:
+--------+------------+------------+-----------+
| rownum | entryDate | particular | accountID |
+--------+------------+------------+-----------+
| 3 | 2015-10-02 | Item3 | 1 |
| 2 | 2015-10-01 | Item2 | 1 |
| 1 | 2015-10-01 | Item1 | 1 |
| 6 | 2015-10-03 | Item6 | 2 |
| 5 | 2015-10-02 | Item5 | 2 |
| 7 | 2015-10-05 | Item7 | 3 |
+--------+------------+------------+-----------+
Also, if it helps, I've tested this on MySQL workbench, SQLbuddy, PHP (the web application), and PHPMyAdmin and they all produce the same result