4

Lets say I have the following table

-----------------------------
| user_id   | comment       |
-----------------------------
| 2         | thats cool    |
| 2         | awesome       |
| 3         | i hate this   |
| 3         | okay          |
| 6         | this is weird |
| 6         | hello?        |
| 6         | what is it    |
| 9         | how are you   |
| 16        | too slow      |
| 16        | yes           |
| 17        | alrighty      |
-----------------------------

How can you select two rows per user_id? So my results would be:

-----------------------------
| user_id   | comment       |
-----------------------------
| 2         | awsome        |
| 2         | thats cool    |
| 3         | i hate this   |
| 3         | okey          |
| 6         | this is weird |
| 6         | hello?        |
| 9         | how are you   |
| 16        | too slow      |
| 16        | yes           |
| 17        | alrighty      |
-----------------------------

Is this possible with a single efficient query? Or are sub-selected necessary?

5
  • 2
    Originally incorrectly posted as an "answer" to this question, Prateek's question is looking for a modification that will provide two rows per group. Edited question accordingly.
    – Paul White
    Aug 2 '15 at 13:25
  • Thank's @Paul, makes more sense now. I'll remove my assumption from my answer since it is no longer necessary.
    – Lennart
    Aug 2 '15 at 13:50
  • Add the output of SHOW CREATE TABLE tablename; in the question. Does the table not have any unique key? Aug 2 '15 at 14:55
  • Also: you want 2 rows per user_id but which 2 rows? How are the 2 to be selected? (for user_id=6 for example there are 3 rows, why 'this is weird' and 'hello?' were selected and not 'what is it' ?) Aug 2 '15 at 14:57
  • Related tips: groupwise-max.
    – Rick James
    Aug 2 '15 at 19:04
3

You can use the technique described in:

http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

to mimic:

row_number() over (partition by ... order by ...)

In your case that would be something like:

SELECT user_id, comment, row_number 
FROM (
    SELECT @row_number:=CASE WHEN @user_id=user_id
                             THEN @row_number+1                                
                             ELSE 1                          
                        END AS row_number
         , @user_id:=user_id AS user_id
        , comment     
    FROM t        
       , (SELECT @row_number:=0,@user_id:='') AS u     
    ORDER BY user_id, comment 
) as v 
WHERE row_number <= 2;
4
  • 1
    Is this a documented, guaranteed technique in MySQL? Aug 2 '15 at 14:11
  • 2
    I don't think so, all and all user variables appears to be a hack that more or less happens to work. The docs at: dev.mysql.com/doc/refman/5.7/en/user-variables.html mentions that the order of evaluation for expressions involving user variables is undefined. In practice it appears to be working, but I would not bet my life on it.
    – Lennart
    Aug 2 '15 at 14:35
  • 2
    Exactly, in MariaDB, the differences in the optimizer may result in not the wanted outcome. It has to do with the ORDER BY being optimized out of the query.) Aug 2 '15 at 14:48
  • I wonder whether it is possible to trick the optimizer by adding LIMIT <large number> in the inner select. In theory the ORDER BY can be optimized out anyway if it is guaranteed that the limit is larger than the size of the sub-select, but I doubt that any optimizer would do that.
    – Lennart
    Aug 2 '15 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.