Mysql complex sorting by multiple columns

Question

I have following MySQL db table filled with data:

|id|parentId|position
|20|NULL    |1
|21|NULL    |2
|22|NULL    |3
|23|21      |1
|24|21      |2

Is it possible to sort it to get final result as:

|id|parentId|position
|20|NULL    |1
|21|NULL    |2
|23|21      |1
|24|21      |2
|22|NULL    |3

As you may notice, position inside parent starts from 1.

So I want it sorted by three columns: id, parentId and position. Any ideas?

Tim Biegeleisen · Accepted Answer · 2018-02-27 11:55:59Z

3

The sorting logic you want is to sort first by the parentId group. This can be obtained using COALESCE(parentId, id). In other words, if the parentId is present, then use it, otherwise take the id, which must also be the parent. Then, within parent group, sort NULLs first. Finally, within those two groups, sort by position.

SELECT *
FROM yourTable
ORDER BY
    COALESCE(parentId, id),
    parentID,
    position;

Demo

edited Feb 27, 2018 at 11:55

answered Feb 27, 2018 at 11:33

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yogesh Sharma · Accepted Answer · 2018-02-27 11:36:33Z

2

I think you want something ?

ORDER BY IFNULL(parentId, id), parentId, position

answered Feb 27, 2018 at 11:36

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

Mysql complex sorting by multiple columns

2 Answers 2

Demo

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related