0

I want to select 1000 rows from another table using the last 1000 ID from another table. This is the query but it returned an error message. What did I do wrong with this query?

SELECT * FROM table1
WHERE id IN
(
SELECT id FROM table2
LIMIT 50
)

Error message received.

Error Code : 1235
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
Cryssie
  • 3,047
  • 10
  • 54
  • 81
  • possible duplicate of [MySQL - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery](http://stackoverflow.com/questions/17892762/mysql-this-version-of-mysql-doesnt-yet-support-limit-in-all-any-some-subqu) – codingbadger Jul 02 '14 at 08:17
  • In any case, in the absence of an ORDER BY clasue, LIMIT is pretty much meaningless – Strawberry Jul 02 '14 at 08:20

3 Answers3

1
SELECT * 
  FROM table1 x
  JOIN 
     ( SELECT id FROM table2 ORDER BY id LIMIT 50 ) y
    ON y.id = x.id;
Strawberry
  • 33,750
  • 13
  • 40
  • 57
0

You should join both tables and sort in descending order and then apply the limit.

SELECT table1.* FROM table1
 JOIN table2 USING (id)
 ORDER BY id DESC
 LIMIT 1000;

This will give you 1000 entries with highest idin descending order if, and only if, they exist in both tables.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
vhu
  • 12,244
  • 11
  • 38
  • 48
  • @Strawberry I'm not sure I follow. I understood that he is after the 1000 rows which a) have highest ID in table2 b) have corresponding row in table1. I changed the result set to only include rows from table1. – vhu Jul 02 '14 at 08:33
  • Hm, fair enough - your logic may in fact be better than mine. Apologies. – Strawberry Jul 02 '14 at 08:41
-2

It is easier to do it like this:

SELECT * FROM TBL1, TBL2 
WHERE TBL2.FK_ID = TBL1.ID 
ORDER BY TBL2.ID
    ASC LIMIT 0,1000

you can only do it if the second table has table1's id as a foreign key, but still has its own id identifying the order of when they are created.

Not sure about how to use the limit though.

I hope it helps.

Ray
  • 31
  • 1
  • 5