CODEWITHSUNDEEP
mysqlsql
Abhishek Ramachandran
Abhishek Ramachandran

Reputation: 1170

SELECT Where ID in (List of IDs) and limit records of each IDs in MySQL

I have met a situation that I have a list of IDs of a Store table and need to fetch the latest 10 files from each store.

SELECT * 
FROM tblFiles 
WHERE storeId in (IDs) 
ORDER BY createdDate DESC  
LIMIT 10

But, this limits the whole results. I found an answer to a similar SO question. But, the answer recommends using loop for each ID. This results in multiple DB hit.

Another option is to fetch all records and group them in the code. But, this will be heavy if there are large no.of records.

It'll be nice if it can be handled at the query level. Any help will be appreciated.

NB: The tables used here are dummy ones.

Upvotes: 2

Views: 9091

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Pre-MySQL 8.0, the simplest method is probably variables:

select f.*
from (select f.*,
             (@rn := if(@s = storeId, @rn + 1,
                        if(@s := storeId, 1, 1)
                       )
             ) as rn
      from (select f.*
            from tblfiles f
            where storeId in (IDs) 
            order by storeId, createdDate desc
           ) f cross join
           (select @s := 0, @rn := 0) params
     ) f
where rn <= 10;

In MySQL 8+ or MariaDB 10.3+, you would simply use window functions:

select f.*
from (select f.*,
             row_number() over (partition by storeid order by createdDate desc) as seqnum
      from tblfiles f
     ) f
where seqnum <= 10;

In older versions of MySQL and MariaDB, the innermost subquery may not be needed.

Upvotes: 2

Ram
Ram

Reputation: 84

use select in where

SELECT * from tblFiles where storeId in (SELECT id from store ORDER BY datefield/id field desc limit 10)

Upvotes: 3

GMB
GMB

Reputation: 222482

You could workaround it with an UNIONed query, where each subquery searches for a particular id and enforces a LIMIT clause, like :

(SELECT * 
FROM tblFiles
WHERE storeId = ?
ORDER BY createdDate DESC
LIMIT 10)
UNION
(SELECT * 
FROM tblFiles
WHERE storeId = ?
ORDER BY createdDate DESC
LIMIT 10)
...

With this solution only one db hit will happen, and you are guarantee to get the LIMIT on a per id basis. Such a SQL can easily be generated from within php code.

Nb : the maximum allowed of UNIONs in a mysql query is 61.

Upvotes: 1

Related Questions