select n values per group

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?

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;

StackExchange Database Administrators Q#108783, answer score: 3