mysql limit subquery,替代在MYSQL中的SubQuery中使用LIMIT关键字

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.

E.g.

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;