-2

I have the same problem as mentioned in In SQL, how to select the top 2 rows for each group. The answer is working fine. But it takes too much time. How to optimize this query?

Example: sample_table

act_id:  act_cnt:
1        1 
2        1 
3        1 
4        1 
5        1 
6        3 
7        3 
8        3 
9        4 
a        4 
b        4 
c        4 
d        4 
e        4

Now i want to group it (or using some other ways). And i want to select 2 rows from each group. Sample Output:

act_id:   act_cnt: 
1         1
2         1
6         3
7         3
9         4
a         4

I am new to SQL. How to do it?

Community
  • 1
  • 1
user3275435
  • 1
  • 5
  • 1
    Please form a self contained question and tell us what query exactly you're using on what table with what indices. "Same as over there" may or may not be accurate and is quite vague. – deceze May 29 '14 at 10:43
  • Is this a homework assignment? – Sean Summers May 29 '14 at 16:41

1 Answers1

3

The answer you linked to uses an inefficient workaround for MySQL's lack of window functions.

Using a window function is most probably much faster as you only need to read the table once:

select name,
       score
from (
   select name,
          score, 
          dense_rank() over (partition by name order by score desc) as rnk
   from the_table
) t
where rnk <= 2;

SQLFiddle: http://sqlfiddle.com/#!15/b0198/1

Having an index on (name, score) should speed up this query.

Edit after the question (and the problem) has been changed

select act_id,
       act_cnt
from (
   select act_id,
          act_cnt, 
          row_number() over (partition by act_cnt order by act_id) as rn
   from sample_table
) t
where rn <= 2;

New SQLFiddle: http://sqlfiddle.com/#!15/fc44b/1

  • This query do not take 2 rows from each groups. It take all rows. – user3275435 May 29 '14 at 12:57
  • @user3275435: my first query was the correct answer to your initial question (actually to the question you linked to). As you didn't supply the actual problem, it was the only thing I could use. See my second query that matches your new problem/sample data –  May 29 '14 at 14:55
  • Yes. Second one is working great. I am very sorry. It's my mistake. I didn't express my question clearly. – user3275435 May 31 '14 at 05:05