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 &lt;= 2;