1

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

Jujunol
  • 457
  • 5
  • 18
  • Why is there a sub select query? what is its purpose? – Altaf Hussain Nov 01 '15 at 06:08
  • 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
  • 1
    your query actually gives exactly that result youve listed - what is the problem ? – amdixon Nov 01 '15 at 06:16
  • 3
    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
  • 2
    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

2 Answers2

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
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.

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