SELECT query without last N rows

Question

How to select all rows of a mysql table without last N(any integer value) rows.

I have tried

SELECT * FROM 
           chat 
         WHERE chat_id NOT IN(
                    SELECT chat_id FROM chat ORDER BY date_time DESC LIMIT 5
                );

But it gives following error

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

I am getting current MySQL version as following

mysql> SHOW VARIABLES LIKE 'version';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| version       | 5.1.33-community |
+---------------+------------------+

score 2 · Accepted Answer · answered Jun 09 '14 at 06:31

2

Use a LEFT JOIN to filter out rows matching a criteria.

SELECT c1.*
FROM chat AS c1
LEFT JOIN (SELECT chat_id
           FROM chat
           ORDER BY date_time DESC
           LIMIT 5) AS c2 ON c1.chat_id = c2.chat_id
WHERE c2.chat_id IS NULL

answered Jun 09 '14 at 06:31

Barmar

669,327
51
454
560

SELECT query without last N rows

1 Answers1