How to control nulls-first or nulls-last in MySQL sorting?

Question

My data looks like this:

App ID  Ref Id  App Type  Reg Date
1       1       Main      2017-05-13
2       2       Sub       2017-05-14
3       1       Sub       2017-05-16
4       2       Main      2017-05-15
5       3       Main      2017-05-14
6       1       sub       2017-05-17
7      null     Main      2017-05-20

I would like to change this table as shown below.

App ID  Ref Id  App Type Reg Date
7      null     Main     2017-05-20
4       2       Main     2017-05-15
2       2       Sub      2017-05-14
5       3       Main     2017-05-14
1       1       Main     2017-05-13
6       1       sub      2017-05-17
3       1       Sub      2017-05-16

The contents with the same ref ID are displayed, and the content with the Main is positioned at the top. The most recently registered content must be at the top. That is, I want to create a hierarchy.

I configured the query as shown below.

SELECT * 
FROM t 
JOIN (SELECT `Ref Id`, MAX(`Reg Date`) AS maxdate FROM t WHERE `App Type` = 'Main' GROUP BY 1) md USING(`Ref Id`) 
ORDER BY maxdate DESC, `Ref Id`, (`App Type` = 'Main') DESC;

However, the results are as follows. I am sorting by Reg Date even if Ref Id is null. In conclusion, to be configured as the second table, data 7 should be at the top.

App ID  Ref Id  App Type  Reg Date    maxdate
4       2       Main     2017-05-15  2017-05-15
2       2       Sub      2017-05-14  2017-05-15
5       3       Main     2017-05-14  2017-05-14
1       1       Main     2017-05-13  2017-05-13
6       1       sub      2017-05-17  2017-05-13
3       1       Sub      2017-05-16  2017-05-13
7      null     Main     2017-05-20    null

Please use quotes for make your information readable. Also, if you can post results on table format, better. — Sakura Kinomoto, Jun 01 '17 at 09:45
The issue is that the join to the subquery fails due to the null `Ref Id`. Perhaps instead of `ORDER BY maxdate` you could `ORDER BY COALESCE(maxdate,'Reg Date')`. — Steve Lovell, Jun 01 '17 at 10:03
That last comment should have backticks rather than single quotes round `Reg Date` but I'm not sure how to prevent that doing odd things to the fomatting. — Steve Lovell, Jun 01 '17 at 10:04
@SteveLovell Ihave fixed this by adding that part.ORDER BY COALESCE(maxdate,'Reg Date') Thanks. — Youngho Lee, Jun 02 '17 at 06:50

score 1 · Answer 1 · answered Jun 01 '17 at 10:10

You are almost there, use left join to get all the records, try following:

SELECT t.* 
FROM t 
LEFT JOIN (
    SELECT `Ref Id`, MAX(`Reg Date`) AS maxdate
    FROM t
    WHERE `App Type` = 'Main'
    GROUP BY 1
) md USING(`Ref Id`) 
ORDER BY t.`Ref Id` is not null, maxdate DESC, (`App Type` = 'Main') DESC, t.`Reg Date` DESC;

score 0 · Answer 2 · answered Jun 02 '17 at 09:05

Re-writing my earlier comment as an answer, and providing a little more context and advice. I think the code should be roughly as follows:

SELECT
    t.*,
    md.MaxDate
FROM
    t 
LEFT JOIN
    (
     SELECT
        `Ref Id` as RefId,
        MAX(`Reg Date`) AS MaxDate
     FROM t
     WHERE
        `App Type` = 'Main'
     GROUP BY
         RefId
    ) md ON md.RefId = t.`Ref Id`
ORDER BY
    COALESCE(md.MaxDate, t.`Reg Date`) DESC,
    t.`Ref Id`,
    (t.`App Type` = 'Main') DESC;

Thoughts on Joins

You'll notice that I've replaced your "Natural Join" with a standard (left outer) Join. I quite like the elegance of Natural Joins, but there seem to be good reasons not to use them (see this SO question and the various answers).

I'm guessing you actually already had this as an outer join as otherwise I'd expect your code not to have returned the problematic row at all (since `Ref Id` is NULL and that's what you're joining on) ... and AFAIK, there is no ANSI_NULLS OFF option in MySQL.

Thoughts on this as a Solution

Now, my main reason for converting my comment to an answer is to point out possible problems with this solution. Much depends on the reason for a record having `Ref Id` = NULL, and what else can be expected in this situation. If you have NULLs where there is both a `App Type` of 'Main' and and `App Type` of 'Sub', then these could be separated from each other due to having a different `Reg Date`. The other related records show in contiguous rows because they are sorted by the shared MaxDate which is retrieved by the subquery, but in this case the subquery is failing and using `Reg Date` as a proxy.

This will not be a problem if the extra 'Sub' type records only appear after a `Ref Id` is assigned.

If that can happen, then you need another way to bind these rows together in the subquery. Not knowing the data, I don't know whether that would be available or not.

P.S. Goodness me I really don't like all these backticks. You don't need them if you don't have spaces in your column-names. If you ask me, that's an excellent reason to avoid putting those spaces in to begin with (if it's your decision).

How to control nulls-first or nulls-last in MySQL sorting?

2 Answers2