CODEWITHSUNDEEP
mysqlsql
Bounce
Bounce

Reputation: 2095

Mysql complex sorting by multiple columns

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?

Upvotes: 1

Views: 62

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

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;

enter image description here

Demo

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I think you want something ? 

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

Upvotes: 2

Related Questions