Unordered N records for each group of grouped results

Question

I have a table containing user records, and I want to take 5 records per user_id. I don't care about any sort of order. I could do this to get 1 record per user:

SELECT * FROM records GROUP BY user_id

I could also do something with user variables to take the top N records. However, my database is very large and a query with user variables isn't efficiently using the index on the user_id column because it has to sort within each group. I don't care about order at all, so I shouldn't have to touch records that aren't relevant. Since I only want 5 and each user has 200-400 records, this is a big performance hit.

Is there a way to write this query efficiently?

This question is not the same as asking how to get the top N records, because I don't care about ordering and I believe that removing that restriction should allow an efficient rewriting. If this is not the case, please explain why not. I have clarified this in the title.

You can also create a loop with distinct user_id and get and store limit 5 records of each user in temporary table, at last select from it. — Shushil Bohara, Sep 15 '16 at 17:52

Unnikrishnan R · Accepted Answer · 2016-09-15T18:13:47.700

2

Try out with the below query. The sub query will number the row based on the column mentioned in the Order By clause. In the outer query you can give the filter criteria.

SET @rowNum = NULL, @rowVal = NULL;
SELECT * FROM (
    SELECT
        *, 
        @rowNum := IF(@rowVal = userid, @rowNum + 1, 1) AS Rno,
        @rowVal := userid AS Dummy
    FROM Yourtable
    ORDER BY [user_id] 
) AS t
 WHERE Rno <= 5

edited Sep 15 '16 at 18:13

answered Sep 15 '16 at 17:55

Unnikrishnan R

4,965
1
12
21

Will this run on MySQL? – jarlh Sep 15 '16 at 17:58
This would query the entire table *before* filtering the rows – Barranka Sep 16 '16 at 17:23

score 0 · Answer 2 · answered Sep 15 '16 at 17:35

0

You can resolve this using a view along with a Partition:

Create a view querying the core table ( add a RowId column counting how many records per user_id):

SELECT *, ROW_NUMBER() OVER(PARTITION BY User_id) AS RowID
FROM Records

lets assume you call the view above ^ "Recordsvw"

It's simple, now of you need only 5 records per user_id query the view that you created above like this:

SELECT *
FROM Recordsvw
WHERE ROwID <= 5

answered Sep 15 '16 at 17:35

sheeni

377
1
17

Does MySQL really support ROW_NUMBER() and PARTITION? – jarlh Sep 15 '16 at 17:39
Im sorry you can't use the exact snippet on MySQL. Please look at the link http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/. This link explain how to simulate ROW_NUMBER() in MySQL. Sorry again. – sheeni Sep 15 '16 at 17:44
MySQL has announced that they intend to implement windowing functions in MySQL 8, but that's at least months away. – Bill Karwin Sep 15 '16 at 17:58
Having that function built in will be deff better than improvising it – sheeni Sep 15 '16 at 18:05

Unordered N records for each group of grouped results

2 Answers2