CODEWITHSUNDEEP
phpmysqlsql
John Cliven
John Cliven

Reputation: 1241

Issues with MySQL SELECT ordering

This is not a question of how to optimize the following query for speed, I am aware there are improvements that could be made, but this is to do with the statement's functionality, which does not seem to be selecting data correctly.

Problem:

The following query should select users from a list of usernames, ordering them by when they were last messaged (oldest first). Some of the usernames in this list will have never been messaged, and as such last_message_date will be NULL. However, the current statement returns an order that puts users that have already been messaged (i.e, last_message_date contains a date) before users that have last_message_date as NULL.

The weird thing is occasionally it will return it ordered correctly, NULL first, other times it wont. I'm scratching my head here.

Code:

SELECT DISTINCT username
FROM `account_usernames`
WHERE (`in_progress` = 0)
  AND (account_source IN
         (SELECT DISTINCT `username`
          FROM source_accounts
          WHERE group_users = 'USA Based'
            AND (`type` = 'users'
                 OR `type` = 'both')
            AND `use` = '1'))
  AND (username NOT IN
         (SELECT user_tomsg
          FROM `message_history`
          WHERE owner_account = 'CurrentOwner'))
  AND (username NOT IN
         (SELECT DISTINCT `username`
          FROM `follower_list`))
ORDER BY last_message_date LIMIT ?
FOR
UPDATE;

As said, this occasionally prioritizes users who have been already been messaged, above users who have never been messaged. If anybody can spot an error from this query a response would be warmly appreciated.

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

If you want NULL values first, then explicitly say so in the ORDER BY:

ORDER BY (last_message_date is null) desc,
         last_message_date

The documentation is clear on where NULLs are placed when using ORDER BY:

When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC.

The use of ASC or DESC probably accounts for the differences you see in the placement of these values.

Upvotes: 2

Related Questions