1

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 |
+---------------+------------------+
MD SHAHIDUL ISLAM
  • 14,325
  • 6
  • 82
  • 89

1 Answers1

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
Barmar
  • 741,623
  • 53
  • 500
  • 612