CODEWITHSUNDEEP
mysqllimit
Manish
Manish

Reputation: 247

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.

Upvotes: 1

Views: 153

Answers (1)

phreakv6
phreakv6

Reputation: 2165

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]>

Upvotes: 2

Related Questions