Question: I have a simple table:

`id`, `status`, `date`

When `status` is `null`, the `date` is also `null`. Question: how do I get my data that's always sorted by `status = null` and then sorted by `date`? The tricky part is, I would like to paginate through results, so I need to be able to use `LIMIT` and `OFFSET` with this query.

Answer: 
Use the [ISNULL()](https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_isnull) function

```
SELECT * FROM mytable
ORDER BY ISNULL(status) DESC,date;

```

or

```
SELECT * FROM mytable
ORDER BY 1-ISNULL(status),date;

```

For pagination, you can reference `id`

```
SELECT B.* FROM
(
    SELECT id FROM mytable
    ORDER BY ISNULL(status) DESC,date
    LIMIT xxx OFFSET yyy
) A INNER JOIN mytable B USING (id)
ORDER BY ISNULL(B.date) DESC,B.date;

```
Are there cases where `date IS NULL` but `status IS NOT NULL`? Assuming not, then

```
ORDER BY `date`  -- gets nulls, then dates ascending
ORDER BY `date` DESC  -- gets latest dates first, ends with nulls

```

With this, `INDEX(date)` should be somewhat efficient for pagination.

If you need nulls first, then latest, you need something like Rolando's suggestions. Alas, no index will be useful.

[*More on pagination*](https://mariadb.com/kb/en/mariadb/partition-maintenance/) and why `OFFSET` is bad.