Looking for a way to limit SQL results 3 results per a specific column

Question

Hello,

I have a list of 9 user id's and want to select 3 entries per user id.

My current query looks like this.

SELECT * FROM entries WHERE user_id IN (1,2,3,4,5,6,7,8,9) LIMIT 27

Some users have many more than 3 entries, so I want to limit the results of this query to 3 entries per user.

I tried making a UNION of 9 separate queries where each queries has its own limit of 3, but this is very slow on the server.

Already I've looked for an answer to this on SO and I've found similar questions, but nothing matching the problem I am having. Any ideas?

Answer 1

If you are merely selecting 3 random entries, then a UNION is probably the best approach.

I find it hard to believe that a simple UNION is "very slow on the server" unless you have a poorly indexed table, or are selecting some huge columns. Do you really need to SELECT *? Probably not.

Please update your question with SHOW CREATE TABLE entries\G as well as the output of EXPLAIN SELECT ... UNION ... \G

Answer 2

Try the following

SELECT TOP 3 * FROM entries
WHERE user_id = 1

UNION ALL


SELECT TOP 3 * FROM entries
WHERE user_id = 2

UNION ALL

...

Answer 3

Assuming that the table entries has another column id as its unique identifier, following query should work (not tested):

SELECT      e.*
FROM        entries e
INNER JOIN (SELECT      e.id
            FROM        entries e
            INNER JOIN  entries x
                    ON  e.user_id = x.user_id
                    --// define the field (ID, TimeStamp) here to specify which top 3, 
                    --// and use sign to specify ASC/DESC (<=, >=)
                    AND e.id <= x.id 
            GROUP BY    e.id
            HAVING      COUNT(*) <= 3 --//only top 3
            ) f
        ON  e.id = f.id

Using this query you can also define which 3 rows you would like to see (order by ID/TimeStamp, ASC or DESC) If the table entries has only few columns that you could GROUP BY all the columns in the table and avoid sub-select.

Answer 4

You can use auto_increment columns as the final component of a composite key to act as a counter of how many values match the other components of the key.

EG.

create temporary table temptab ( userid int not null, 
                                 rank int auto_increment not null,
                                 primary key (userid, rank ) );

insert into temptab select userid, NULL from entries order by userid;  

# change the "order by" to fit your criteria - eg. order by last_used desc, ...

delete from temptab where rank > 3;
select * from temptab order by userid, rank;

should return:

   1, 1
   1, 2
   1, 3
   2, 1
   ...

This will need tuning to fit your uses. Probably also insert the primary key from the entries table into the temporary table so that a join from temptab to entries can produce some useful data.