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?

Show us sample data and the results you want. – pilcrow Oct 10 '12 at 03:19 — pilcrow, Oct 10 '12 at 03:19

score 70 · Answer 1 · edited Mar 12 '19 at 11:43

70

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;

edited Mar 12 '19 at 11:43

someone811

7
1
3

answered Apr 20 '16 at 11:28

aneetkukreja

847
6
5

5

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? – edeesan Feb 13 '18 at 03:59
1

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. – aneetkukreja Feb 15 '18 at 07:12

score 39 · Answer 2 · answered Aug 16 '18 at 12:58

39

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

answered Aug 16 '18 at 12:58

yash

2,101
2
23
32

2

Thank you, this solution works and deserves acceptance – Arthur Khazbs Jun 16 '21 at 14:11
1

So simple, and works perfectly. **This** should be the accepted answer. – S3DEV Jun 17 '21 at 08:37

score 8 · Answer 3 · answered Oct 10 '12 at 00:38

8

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

answered Oct 10 '12 at 00:38

pilcrow

56,591
13
94
135

I tried this one. It still returns me like 84 rows and they are not unique as well....:( – Pi Horse Oct 10 '12 at 00:50
@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. – pilcrow Oct 10 '12 at 00:59
I just executed the query which you wrote above – Pi Horse Oct 10 '12 at 03:00

score 0 · Answer 4 · answered Jun 08 '22 at 19:33

0

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
    )

answered Jun 08 '22 at 19:33

Damian Chudobiński

112
1
12

score -2 · Answer 5 · edited Jul 18 '22 at 08:34

-2

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

edited Jul 18 '22 at 08:34

Dragonthoughts

2,180
8
25
28

answered Jul 16 '22 at 10:04

user19561251

1

4

Some explanation, and formatting, might help to understand why you think this answer adds value to this question. – Luuk Jul 16 '22 at 10:11

score -14 · Accepted Answer · edited Apr 24 '15 at 12:21

-14

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;

edited Apr 24 '15 at 12:21

Layke

51,422
11
85
111

answered Oct 10 '12 at 04:14

Sara

2,417
7
35
52

Start from SELECT *. The first line! – Sara Oct 10 '12 at 04:46
11

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. – Shubham Baranwal Dec 21 '16 at 13:11
The derived table answer seems to be the more correct one. – Mladen Mihajlovic Oct 30 '17 at 16:13

Alternative to using LIMIT keyword in a SubQuery in MYSQL

6 Answers6

Linked