MySQL: Group by select first 2 rows in each group

Question

Is there a way to select first 2 answers for each Qs from answers table:

user_answers Table structure:

id    question_id    user_id    answer_id    create_date
1         1             9           5            null
2         2             8           7            null
3         1             1           3            null
4         3             4           20           null
5         1             4           5            null
6         4             3           25           null
7         2             7           5            null
8         4             9           26           null
9         2             5           8            null
10        1             1           5            null

I need to return results like this:

id    question_id    user_id    answer_id    create_date
1         1             9           5            null
3         1             1           3            null
2         2             8           7            null
7         2             7           5            null
4         3             4           20           null
6         4             3           25           null
8         4             9           26           null

It's like Group by "question_id" but select first 2 rows from each group,

thanks,

*"It's like Group by "question_id""* -- no, it's not like `GROUP BY`. `GROUP BY` **does not** return rows from the table; it **computes** the content of the rows it returns using the data from each group. This question belongs to the tag [tag:greatest-n-per-group] — axiac, May 11 '17 at 09:30
thank for inform me, so is there anyway to handle it in MySQL? — mwafi, May 11 '17 at 09:32

Tim Biegeleisen · Answer 1 · 2017-05-11T09:49:30.120

2

This is a canonical problem where a ROW_NUMBER analytic function would be extremely useful. MySQL does not support any row number functionality out of the box, but we can simulate it using session variables:

SET @row_num = 0;
SET @q_id = 0;

SELECT
    t.id,
    t.question_id,
    t.user_id,
    t.answer_id,
    t.create_date
FROM
(
    SELECT 
        @row_num:=CASE WHEN @q_id = question_id THEN @row_num + 1 ELSE 1 END AS rn,
        @q_id:=question_id as question_id,
        id,
        user_id,
        answer_id,
        create_date
    FROM
        user_answers
    ORDER BY question_id, id
) t
WHERE t.rn <= 2
ORDER BY t.question_id, t.id;

Output:

Demo here:

Rextester

edited May 11 '17 at 09:49

answered May 11 '17 at 09:35

Tim Biegeleisen

502,043
27
286
360

Run this Query on MySQL (phpMyadmin) "@row_num" variable always have: 1 – mwafi May 11 '17 at 09:47
@mwafi Hmm...it runs in the demo. Try initializing with `SET @q_id = 0;` instead and see if that changes anything. You may have to slightly tweak this query to get it to run in your environment. – Tim Biegeleisen May 11 '17 at 09:50

Oto Shavadze · Accepted Answer · 2017-05-11T09:55:34.400

2

One way: (if you need more than 2 rows per group, then this is not solution)

select your_table.* from your_table
inner join(
    select min(id) as id from your_table group by question_id
    union all
    select min(id) as id from your_table
    where id not in (select min(id) from your_table group by question_id)
    group by question_id
) t
on your_table.id = t.id
order by your_table.question_id , your_table.id

edited May 11 '17 at 09:55

answered May 11 '17 at 09:48

Oto Shavadze

40,603
55
152
236

Work fine, pls, how can I display first 3? rather than 2 – mwafi May 11 '17 at 10:01
Just not this )), for more than 2 rows per group, this query becomes very ugly and more inefficient, so if you need more than 2 rows per group, then this is not solution . – Oto Shavadze May 11 '17 at 10:06
1

@mwafi: To get the minimums plus the minimums of all values except those minimums in order to get the first two minimums already feels a bit clumsy. And as Oto says himself: this gets more clumsy with more than two rows. You'd use a row numbering technique instead as shown in Tim's and my answer. – Thorsten Kettner May 11 '17 at 11:13
@ThorstenKettner - If needed just 2 first/last rows, this is probably one of most efficient way in mysql. About more than 2 rows, this is bad idea, I agree without doubt. – Oto Shavadze May 11 '17 at 11:52
@Oto Shavadze: And I agree that the idea is good for two rows :-) It looks a bit clumsy, but does its task efficiently. – Thorsten Kettner May 11 '17 at 12:31

score 1 · Answer 3 · answered May 11 '17 at 09:33

1

There is a simple but rather slow solution: count the records.

select *
from answers
where
(
  select count(*)
  from mytable other
  where other.questionid = answers.questionid
  and other.id <= answers.id
) <= 2
order by questionid, id;

answered May 11 '17 at 09:33

Thorsten Kettner

89,309
7
49
73

VERY slow indeed. I ran a similar query to this method and it took 9 seconds. – rolinger May 16 '22 at 15:32

MySQL: Group by select first 2 rows in each group

3 Answers3

Rextester