Query result sorting mismatch when using limit and offset MySQL

Issue

This Content is from Stack Overflow. Question asked by Dipu Ahmed

I am getting 2 kinds of results When I am using limit and offset in the query.

Query without limit

 select
  `mobile_app_user_id`
from
  `store_visits`
where
  (
    exists (
      select
        *
      from
        `mobile_app_users`
      where
        `store_visits`.`mobile_app_user_id` = `mobile_app_users`.`id`
        and CONCAT(
          mobile_app_users.first_name,
          ' ',
          mobile_app_users.last_name
        ) LIKE '%%'
        and `mobile_app_users`.`deleted_at` is null
    )
    or exists (
      select
        *
      from
        `sales_partners`
      where
        `store_visits`.`sales_partner_id` = `sales_partners`.`id`
        and `company_name` LIKE '%%'
    )
  )
order by
  `check_in_time` desc

Query With Limit

select
  `mobile_app_user_id`
from
  `store_visits`
where
  (
    exists (
      select
        *
      from
        `mobile_app_users`
      where
        `store_visits`.`mobile_app_user_id` = `mobile_app_users`.`id`
        and CONCAT(
          mobile_app_users.first_name,
          ' ',
          mobile_app_users.last_name
        ) LIKE '%%'
        and `mobile_app_users`.`deleted_at` is null
    )
    or exists (
      select
        *
      from
        `sales_partners`
      where
        `store_visits`.`sales_partner_id` = `sales_partners`.`id`
        and `company_name` LIKE '%%'
    )
  )
order by
  `check_in_time` desc
limit
  25 offset 0

Note: mobile_app_user_id and chech_in_time can be duplicate in store_visits table

My guess is that the data is same but the sorting of these two queries are different which I am trying to avoid.



Solution

This question is not yet answered, be the first one who answer using the comment. Later the confirmed answer will be published as the solution.

This Question and Answer are collected from stackoverflow and tested by JTuto community, is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?