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?

Amal

Show us sample data and the results you want. – Formulary

Answer 1

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;

Edmundedmunda

Start from SELECT *. The first line! – Edmundedmunda

It not works if there are multiple rows associate with name it just shows you 10 rows for test table. I think this is not the correct solution of question. – Smokejumper

The derived table answer seems to be the more correct one. – Clouse

Answer 2

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;

Cheesy

Why is "(select DISTINCT code_ver from test where code_ver NOT LIKE %DevBld%' ORDER by date DESC LIMIT 10)" not considered a subquery and LIMIT is allowed in this? – Kapor

If we query as a derived source of data for other query like questioner has put in "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); " then we call it sub-query but as per mysql current rules LIMIT is not allowed in sub-query that's why I suggested a JOIN which serves the purpose. – Cheesy

Answer 3

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);

Pleistocene

Thank you, this solution works and deserves acceptance – Chuckhole

So simple, and works perfectly. This should be the accepted answer. – Osculum

Sorry, but isn't this a sub-query? LIMIT does not work in sub-queries, with my version of MySQL server? – Dollar

@CharlesRobertson This solution makes the subquery work with LIMIT by encapsulating it in another query. It works, I just tested it – Atrium

Answer 4

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.)

Formulary

I tried this one. It still returns me like 84 rows and they are not unique as well....:( – Amal

@sagarvikani, then I don't understand what you were trying to do. Suppose you did CREATE TABLE t SELECT DISTINCT code_ver ... LIMIT 10, and then tried your original query with WHERE code_ver IN (SELECT code_ver FROM t). I expect you'd still get 84 rows. – Formulary

I just executed the query which you wrote above – Amal

Answer 5

Maybe a bit late, but you can wrap subquery into another query over it and just take all from subquery

   SELECT * FROM tableWHERE id NOT IN 
    (SELECT * FROM 
        ( SELECT id FROM table LIMIT 0, 2) as SUBQUERY
    )

Saturniid

Answer 6

SELECT * FROM test WHERE code_ver IN ( SELECT code_ver, RANK() OVER (ORDER BY date DESC) AS rank_of_date FROM test WHERE code_ver NOT LIKE '%DevBld%' ) WHERE rank_of_date <= 10 );

Earth

Answer 7

select * from test t1, (select DISTINCT code_ver 
                      from test 
                     where code_ver NOT LIKE '%DevBld%' 
                     ORDER by date DESC LIMIT 10 ) as t2
where t1.code_ver=t2.code_ver

Uppsala

Some explanation, and formatting, might help to understand why you think this answer adds value to this question. – Arrivederci