title: Sequelize limit order issue
body: <p>Currently am having a problem with a simple query.</p>
  
  <h2>Sequelize query:</h2>
  
  <pre><code>db[TABLE_NAMES.BOOKING].findAll({
      order: [
        [ db[TABLE_NAMES.USER_BOOKING_RELATION], db[TABLE_NAMES.BOOKING_STATUS], sequelize.literal('status = \'Attention\''), 'desc'],
        ['created_at', 'desc']
      ],
      offset,
      limit: max,
      attributes: ['id', 'created_at'],
      where: { school_id: schoolId },
      include: [
        {
          attributes: ['id'],
          model: db[TABLE_NAMES.USER_BOOKING_RELATION],
          include: [
            {
              required: true,
              attributes: ['status'],
              model: db[TABLE_NAMES.BOOKING_STATUS],
              where: { status: { [Sequelize.Op.in]: ['Attention', 'Pending', 'Invited'] } }
            },
          ]
        }
      ]
    });
  </code></pre>
  
  <h2>Models</h2>
  
  <pre><code>const booking = sequelize.define(TABLE_NAMES.BOOKING, {
      id: {
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4,
        primaryKey: true,
        unique: true
      },
      created_at: {
        type: DataTypes.DATE,
        defaultValue: sequelize.literal('NOW()')
      },
      updated_at: {
        type: DataTypes.DATE,
        defaultValue: sequelize.literal('NOW()')
      },
      deleted_at: {
        type: DataTypes.DATE
      }
    });
  
  const user_booking_relation = sequelize.define(TABLE_NAMES.USER_BOOKING_RELATION, {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true,
      unique: true
    },
    user_id: {
      type: DataTypes.UUID,
      allowNull: false
    },
    booking_id: {
      type: DataTypes.UUID,
      allowNull: false
    },
    booking_status_id: {
      type: DataTypes.UUID,
      allowNull: false,
    },
    created_at: {
      type: DataTypes.DATE,
      defaultValue: sequelize.literal('NOW()')
    },
    updated_at: {
      type: DataTypes.DATE,
      defaultValue: sequelize.literal('NOW()')
    },
    deleted_at: {
      type: DataTypes.DATE
    },
  });
  
  const booking_status = sequelize.define(TABLE_NAMES.BOOKING_STATUS, {
      id: {
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4,
        primaryKey: true,
        unique: true
      },
      status: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true
      },
      created_at: {
        type: DataTypes.DATE,
        defaultValue: sequelize.literal('NOW()')
      },
      updated_at: {
        type: DataTypes.DATE,
        defaultValue: sequelize.literal('NOW()')
      },
      deleted_at: {
        type: DataTypes.DATE
      }
    });
  
  booking.hasMany(user_booking_relation, { foreignKey: 'booking_id', sourceKey: 'id' });
  user_booking_relation.belongsTo(booking, { foreignKey: 'booking_id', targetKey: 'id' });
  booking_status.hasMany(user_booking_relation, { foreignKey: 'booking_status_id', sourceKey: 'id' });
  user_booking_relation.belongsTo(booking_status, { foreignKey: 'booking_status_id', targetKey: 'id' });
  
  </code></pre>
  
  <h2>Generated sql</h2>
  
  <pre><code>SELECT `booking`.*,
         `user_booking_relations`.`id`                     AS `user_booking_relations.id`,
         `user_booking_relations-&gt;booking_status`.`id`     AS `user_booking_relations.booking_status.id`,
         `user_booking_relations-&gt;booking_status`.`status` AS `user_booking_relations.booking_status.status`
  FROM (SELECT `booking`.`id`, `booking`.`created_at`
        FROM `booking` AS `booking`
        WHERE (`booking`.`deleted_at` IS NULL AND `booking`.`school_id` = 'a97b42e5-c864-4a4a-870b-737dd9700124')
          AND (SELECT `user_booking_relations`.`booking_id`
               FROM `user_booking_relation` AS `user_booking_relations`
                        INNER JOIN `booking_status` AS `booking_status`
                                   ON `user_booking_relations`.`booking_status_id` = `booking_status`.`id` AND
                                      (`booking_status`.`deleted_at` IS NULL AND
                                       `booking_status`.`status` IN ('Invited', 'Pending', 'Attention'))
               WHERE ((`user_booking_relations`.`deleted_at` IS NULL) AND
                      `user_booking_relations`.`booking_id` = `booking`.`id`)
               LIMIT 1) IS NOT NULL
        ORDER BY `booking`.`created_at` DESC
        LIMIT 0, 10) AS `booking`
           LEFT OUTER JOIN ( `user_booking_relation` AS `user_booking_relations` INNER JOIN `booking_status` AS `user_booking_relations-&gt;booking_status` ON
              `user_booking_relations`.`booking_status_id` = `user_booking_relations-&gt;booking_status`.`id` AND
              (`user_booking_relations-&gt;booking_status`.`deleted_at` IS NULL AND
               `user_booking_relations-&gt;booking_status`.`status` IN ('Invited', 'Pending', 'Attention')) )
                           ON `booking`.`id` = `user_booking_relations`.`booking_id` AND
                              (`user_booking_relations`.`deleted_at` IS NULL)
  ORDER BY `user_booking_relations-&gt;booking_status`.status = 'Attention' DESC, `booking`.`created_at` DESC;
  </code></pre>
  
  <h2>Result</h2>
  
  <pre><code>// first page
  // first page
  [
    {
      "id": "4c74c307-3f7c-40c6-ba26-6d0e9f510bcc",
      "created_at": "2020-05-30T20:15:07.000Z",
      "user_booking_relations": [
        {
          "id": "7cb183c7-77cf-4fc8-9c98-eb2b8abf8d39",
          "booking_status": {
            "status": "Attention"
          }
        }
      ]
    },
    {
      "id": "1e8c9250-61b5-4610-b913-bd7aee866d5d",
      "created_at": "2020-06-01T14:48:00.000Z",
      "user_booking_relations": [
        {
          "id": "0a9ba1a0-0929-4979-ba15-12c4903fd8a5",
          "booking_status": {
            "status": "Invited"
          }
        },
      ]
    },
    {
      "id": "a1624f59-ebaa-4bc7-95b8-d0e96c1ec917",
      "created_at": "2020-06-01T08:45:12.000Z",
      "user_booking_relations": [
        {
          "id": "fdbc677b-2035-44d2-8d9a-ab304e5624ee",
          "booking_status": {
            "status": "Pending"
          }
        }
      ]
    }
  ]
  
  // second page
  [
    {
      "id": "d18abf5c-c986-4c2c-a08d-02e1488745d8",
      "created_at": "2020-05-30T20:14:10.000Z",
      "user_booking_relations": [
        {
          "id": "585c0674-13cf-45ac-91bc-087b345a7b31",
          "booking_status": {
            "status": "Attention"
          }
        }
      ]
    },
    {
      "id": "692ccef9-12b4-4aed-955b-11ce65512469",
      "created_at": "2020-05-30T20:12:45.000Z",
      "user_booking_relations": [
        {
          "id": "5c3d214d-833a-482d-aeb5-272af750f3bb",
          "booking_status": {
            "status": "Attention"
          }
        }
      ]
    },
    {
      "id": "1a5a56a9-4a6e-4548-a4d8-b388e6a9ac02",
      "created_at": "2020-05-30T20:09:43.000Z",
      "user_booking_relations": [
        {
          "id": "cea240f4-2529-44ae-a82d-f53d2dbbd0fc",
          "booking_status": {
            "status": "Attention"
          }
        }
      ]
    }
  ]
  </code></pre>
  
  <h2>Expected result</h2>
  
  <pre><code>// first page
  [
    {
      "id": "4c74c307-3f7c-40c6-ba26-6d0e9f510bcc",
      "created_at": "2020-05-30T20:15:07.000Z",
      "user_booking_relations": [
        {
          "id": "7cb183c7-77cf-4fc8-9c98-eb2b8abf8d39",
          "booking_status": {
            "status": "Attention"
          }
        }
      ]
    },
    {
      "id": "d18abf5c-c986-4c2c-a08d-02e1488745d8",
      "created_at": "2020-05-30T20:14:10.000Z",
      "user_booking_relations": [
        {
          "id": "585c0674-13cf-45ac-91bc-087b345a7b31",
          "booking_status": {
            "status": "Attention"
          }
        }
      ]
    },
    {
      "id": "692ccef9-12b4-4aed-955b-11ce65512469",
      "created_at": "2020-05-30T20:12:45.000Z",
      "user_booking_relations": [
        {
          "id": "5c3d214d-833a-482d-aeb5-272af750f3bb",
          "booking_status": {
            "status": "Attention"
          }
        }
      ]
    }
  ]
  
  // second page
  [
    {
      "id": "1a5a56a9-4a6e-4548-a4d8-b388e6a9ac02",
      "created_at": "2020-05-30T20:09:43.000Z",
      "user_booking_relations": [
        {
          "id": "cea240f4-2529-44ae-a82d-f53d2dbbd0fc",
          "booking_status": {
            "status": "Attention"
          }
        }
      ]
    },
    {
      "id": "1e8c9250-61b5-4610-b913-bd7aee866d5d",
      "created_at": "2020-06-01T14:48:00.000Z",
      "user_booking_relations": [
        {
          "id": "0a9ba1a0-0929-4979-ba15-12c4903fd8a5",
          "booking_status": {
            "status": "Invited"
          }
        },
      ]
    },
    {
      "id": "a1624f59-ebaa-4bc7-95b8-d0e96c1ec917",
      "created_at": "2020-06-01T08:45:12.000Z",
      "user_booking_relations": [
        {
          "id": "fdbc677b-2035-44d2-8d9a-ab304e5624ee",
          "booking_status": {
            "status": "Pending"
          }
        }
      ]
    }
  ]
  </code></pre>
  
  <p>As we can see from the generated SQL, bookings with any of those statuses are initially selected and then sorted. I need Attention bookings to go first. How can I get around this or fix it? Any thoughts will be much appreciated.</p>
  
  <p>Thanks,
  Alex.</p>