Alternative to using LIMIT keyword in a SubQuery in MYSQL
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?
The error you are getting is not exactly because of the version of MySQL. I think all versions support that. You have to change the LIMIT 10 place and place it after ")". Let me know if it works for you. I ran the bellow one on mine and it works.
SELECT * FROM test where name IN ( SELECT DISTINCT name FROM projects WHERE name NOT LIKE "%DevBld%" ORDER by date_created DESC ) LIMIT 10;
Update: Try the one below, this way order would work:
SELECT * FROM automation.e2e_projects WHERE name IN ( SELECT DISTINCT name FROM automation.e2e_projects WHERE name NOT LIKE "%DevBld%" ) ORDER by date_created DESC LIMIT 10;
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;
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.)
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);