CODEWITHSUNDEEP
mysql
Jafo
Jafo

Reputation: 1331

LIMIT showing duplicate results

I can't figure out why this is happening. I have a table with the following columns:

+-------------+------------+------+-----+---------+----------------+
| Field       | Type       | Null | Key | Default | Extra          |
+-------------+------------+------+-----+---------+----------------+
| adid        | int(11)    | NO   | PRI | NULL    | auto_increment |
| price       | float      | YES  |     | NULL    |                |
| categoryid  | int(11)    | YES  |     | NULL    |                |
| visible     | tinyint(4) | YES  | MUL | NULL    |                |
+-------------+------------+------+-----+---------+----------------+

There are 7 records in this table that are visible and have category set as 3. I do a simple query like this:

SELECT adid FROM ads as a 
WHERE categoryid = 3 
and visible = 1 
order by price desc 
limit 0, 5

I get the following adid's returned: 1,4,3,15,7

On the next page the query is:

SELECT adid FROM ads as a 
WHERE categoryid = 3 
and visible = 1 
order by price desc 
limit 5, 5

I get: 11,15

Maybe I am up too late, but why do I get 15 twice?

Upvotes: 0

Views: 46

Answers (1)

zerkms
zerkms

Reputation: 254886

For the results to be stable and consistent you need to have any unique column to participate in sorting.

In this case it might be

ORDER BY price DESC, adid

Upvotes: 3

Related Questions