Manish Sharma Manish Sharma - 5 months ago 9
SQL Question

Limit with not operator is not working correctly MySql

I am using a query as below with not operator in where and limit. I am not able get correct sequencing of data (Without limit clause it is different and with limit ordering of data is something different).

SELECT DISTINCT(id) FROM `table` WHERE (1 AND id_type!=1) ORDER BY id desc LIMIT 2


Table Structure with sample data:
id id_type
1 1
2 1
3 2
4 3
5 3
6 3


Any Suggestions ?

Expected Output :

6
5

But When I remove the limit ordering is not same :

5
6
4
3
2


Note: This is just an sample data structure. Original table is contains much fields and different columns.

Answer

Maybe there is no problem at all. See this.

MariaDB [fbb]> SELECT * FROM `test`;
+----+---------+
| id | id_type |
+----+---------+
|  1 |       1 |
|  2 |       1 |
|  3 |       2 |
|  4 |       3 |
|  5 |       3 |
|  6 |       3 |
+----+---------+
6 rows in set (0.00 sec)

MariaDB [fbb]> SELECT DISTINCT(id) FROM `test` WHERE (1 AND id_type!=1) ORDER BY id desc LIMIT 2;
+----+
| id |
+----+
|  6 |
|  5 |
+----+
2 rows in set (0.00 sec)

MariaDB [fbb]> 
Comments