How to grab the last three rows in MySQL

Question

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

Which sub query? If you're referring to the one inside the `from` then it's so that the number of rows per account is able to be limited to 3 per account. The sub query inside the join is used to initialize the variables. — Jujunol, Nov 01 '15 at 06:13
your query actually gives exactly that result youve listed - what is the problem ? — amdixon, Nov 01 '15 at 06:16
I'm also finding the query to solve the problem as explained. Your example may be clearer if you had a little more data with more distinct dates. [SQL Fiddle](http://sqlfiddle.com/#!9/29eeb5/1/0) — Ken Geis, Nov 01 '15 at 06:21
try replacing the variable assignment in one step : `@rownum := if(@account = accountID, @rownum + 1, if(@account := accountID, 1, 1)) as rownum`. also post what mysql version youre using — amdixon, Nov 01 '15 at 06:24
I made an update of what I'm receiving on my end as results. — Jujunol, Nov 01 '15 at 06:25
@KenGeis The SQL fiddle pulls Item1 which is older than the other data so it shouldn't be a results. — Jujunol, Nov 01 '15 at 06:26
for accountID 1 there are two entries on 2015-10-02 and two on 2015-10-01. the third using order ID, then date for this account will have a date of 2015-10-01.. — amdixon, Nov 01 '15 at 06:31
It just errored with the nested if, mySQL version 5.6.25. I don't all understand your other comment, you mean that as result my third would be 2015-10-01 as other two would be ...-02 ? Yes, that's the desired result of the example ? — Jujunol, Nov 01 '15 at 06:48
@amdixon Basically, the goal is to pull the most 3 recent by entryDate so, if works then wouldn't have item1 — Jujunol, Nov 01 '15 at 06:49
in terms of most recent, its not defined if the third one by account 1 is item1 or item2 — amdixon, Nov 01 '15 at 06:51
@amdixon Yes, sorry I had overlooked that. I see what you're saying now... — Jujunol, Nov 01 '15 at 07:04

score 2 · Answer 1 · answered Nov 01 '15 at 12:49

A safe way to write this query is:

select rownum, entryDate, particular, accountID
from (select entryDate, particular, accountID,
             (@rownum := if(@account = accountID, @rownum + 1, 
                            if(@account := accountID, 1, 1)
                           )
             ) as rownum,
      from entries cross join
           (select @rownum := 0, @account := 0 ) init
      order by accountid, entryDate desc
     ) t
where t.rownum <= 3;

This is explained in the documentation:

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement . . . In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:
SELECT @a, @a:=@a+1, ...;
However, the order of evaluation for expressions involving user variables is undefined.

Schwern · Accepted Answer · 2015-11-01T07:16:50.630

1

Your query works for me if I fix the syntax error.

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;

rownum  entryDate            particular         accountID
1       October, 02 2015     Item3              1
2       October, 02 2015     Item4              1
3       October, 01 2015     Item1              1
1       October, 03 2015     Item6              2
2       October, 02 2015     Item5              2
1       October, 05 2015     Item7              3

Note that Item1 and Item2 have the same entryDate. It's not predictable whether you'll get Item1, 3 and 4 or 2, 3 and 4. However, you should never get Item1, 2 and 3.

Also note that rownum is not 1 through 7, but incremented separately for each account. This is correct. That your output differs for the same query. I think I know what happened.

Your very clever subquery relies on the order of operations of columns in the sub-select. In particular, this.

    @rownum := if(@account = accountID, @rownum + 1, 1) rownum,
    @account := accountID

That's the trick which assigns a separate set of rownums to each account. It relies on the fact that the rows are ordered by account and that @rownum is being set before @account.

Problem is, I don't think you can rely on that.

edited Nov 01 '15 at 07:16

answered Nov 01 '15 at 06:42

Schwern

153,029
25
195
336

this out put is different from what he needs... `Item1` should not be there instead `item2` should be there.. Right? – Subin Chalil Nov 01 '15 at 07:01
Right, I had overlooked that Item1 and 2 had the same date in the test data. I've noticed even when Item2's date is switched to *-02 in the SQLFiddle provided before, the result works meaning it has to do with my server I guess :/ – Jujunol Nov 01 '15 at 07:09
@Jujunol I suspect the problem is the order of operations for SELECT columns is not reliable. I'm not entirely sure. – Schwern Nov 01 '15 at 07:17
1

it isnt - see [mysql-user-variable-techniques](http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/). but you can force the evaluation order with function sequence points like `@rownum := if(@account = accountID, @rownum + 1, if(@account := accountID, 1, 1)) as rownum` – amdixon Nov 01 '15 at 07:21
1

Note: this construct is dangerous and may not work under all circumstances because `@account` is being set in one expression and used in another. MySQL does not guarantee the order of processing of variables. See my answer. – Gordon Linoff Nov 01 '15 at 12:46

How to grab the last three rows in MySQL

2 Answers2