Alternative to using LIMIT keyword in a SubQuery in MYSQL

Question

I have a table TEST with the following columns :

code_ver (VARCHAR)
suite (VARCHAR)
date (DATE)

Now I want to select 10 rows with a distinct value of code_ver & code_ver NOT LIKE '%DevBld%' sorted by date desc.

So I wrote the following query:

select * 
  from test 
 where code_ver IN (select DISTINCT code_ver 
                      from test 
                     where code_ver NOT LIKE '%DevBld%' 
                     ORDER by date DESC LIMIT 10);

This query should ideally work, but my version of MySQL says :

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Can someone suggest me an alternative to this query?

Answer 1

Answer suggested by Layke is wrong in my purview. Intention of using limit in subquery is so main query run on limited records fetched from subquery. And if we keep limit outside then it makes limit useless for subquery.

Since mysql doesn't support yet limit in subquery, instead you can use JOIN as follows:

            SELECT * FROM test     JOIN      (         SELECT DISTINCT code_ver          FROM test          WHERE code_ver NOT LIKE '%DevBld%'          ORDER BY date DESC LIMIT 10     ) d     ON test.code_ver     IN (d.code_ver)     ORDER BY xyz; 

Answer 2

You can also use same query, just by adding one extra layer of select before subquery. and that's it. It will work.

select * from test 
where code_ver IN (select * from (select DISTINCT code_ver 
                      from test 
                     where code_ver NOT LIKE '%DevBld%' 
                     ORDER by date DESC LIMIT 10) as t1);

Answer 3

Put the subquery in a derived table:

   SELECT test.*
     FROM test
LEFT JOIN (SELECT DISTINCT code_ver
             FROM mastertest
            WHERE code_ver NOT LIKE '%DevBld%'
            ORDER BY `date` DESC
            LIMIT 10) d
    USING (code_ver)
    WHERE d.code_ver IS NOT NULL;

(You could also RIGHT JOIN that, of course, and drop the outer WHERE condition.)