CODEWITHSUNDEEP
mysql
Robert Lu
Robert Lu

Reputation: 2199

Why offset change, the result is same?

When I execute query 

select * from broker_commission_rate 
where broker_commission_rate.deleted_at is null 
order by settlement_at asc limit 20 offset 520

I got result start with id 560, end with id 584.

When I change offset from 520 to 540, I got same result set. 

select * from broker_commission_rate 
where broker_commission_rate.deleted_at is null 
order by settlement_at asc limit 20 offset 540

Change order by clause to order by settlement_at asc,id asc, the result set has changed.

I want to know why offset doesn't work in this SQL? Thank you in advance.

Table definition:

CREATE TABLE `broker_commission_rate` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sn` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `broker_uid` int(11) NOT NULL DEFAULT '0' ,
  `broker_type` int(11) NOT NULL DEFAULT '0' ,
  `broker_level` int(11) NOT NULL DEFAULT '0',
  `employee_id` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `type` tinyint(4) NOT NULL DEFAULT '0' ,
  `rate` int(11) NOT NULL DEFAULT '0' ,
  `price` decimal(8,2) NOT NULL DEFAULT '0.00' ,
  `broker_price` decimal(8,2) NOT NULL DEFAULT '0.00' ,
  `settlement_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `broker_commission_rate_sn_index` (`sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

System info:

Mysql 5.6.20 @ Linux x85_64

Upvotes: 0

Views: 106

Answers (1)

Shaharyar
Shaharyar

Reputation: 12449

Offset works actually!

As you said when you change order by clause it returns accurate dataset. It means your order by condition was incorrect.

You were sorting by settlement_at, there may be duplicate values in this column so when you added another condition sort by id. It worked fine.

It runs like: If settlement_at is same, look for id to sort.

So your final query should be:

select * 
from broker_commission_rate 
where broker_commission_rate.deleted_at is null 
order by settlement_at asc, id asc 
limit 20 offset 540

Upvotes: 1

Related Questions