Mysql 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

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

So if I query table:

SELECT * FROM myTable ORDER BY IFNULL(parentId, id), parentId, position

I get following results:

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

What is actually fine, until I change the row(id#21) position to value 3,and row#22 position to value 2. Now I would like to expect following results:

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

Unfortunately nothing changes and I have the same order:

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

So any ideas how to change query to get wanted results?

Possible duplicate of Mysql complex sorting by multiple columns — Valerica
– Valerica, Commented Feb 27, 2018 at 14:54
You just posted the same question like 3 hours ago ... if the answer doesn't suit your request just edit the question, don't post another one — Valerica
– Valerica, Commented Feb 27, 2018 at 15:06

trincot · Accepted Answer · 2018-02-27 15:17:15Z

I would suggest to perform an outer join to find the parent record, and then use its position to calculate an order:

SELECT    a.*
FROM      myTable a
LEFT JOIN myTable b ON a.parentId = b.id
ORDER BY  IFNULL(b.position, a.position), 
          IF(b.position, a.position, null);

When you have "grandchildren" in your table, you will need to extend this pattern:

SELECT    a.*
FROM      myTable a
LEFT JOIN myTable b ON a.parentId = b.id
LEFT JOIN myTable c ON b.parentId = c.id
ORDER BY  COALESCE(c.position, b.position, a.position), 
          IF(c.position, b.position, IF(b.position, a.position, null)), 
          IF(c.position, a.position, null);

When there can be grand-grandchildren, then:

SELECT    a.*
FROM      myTable a
LEFT JOIN myTable b ON a.parentId = b.id
LEFT JOIN myTable c ON b.parentId = c.id
LEFT JOIN myTable d ON c.parentId = d.id
ORDER BY  COALESCE(d.position, c.position, b.position, a.position), 
          IF(d.position, c.position, IF(c.position, b.position, IF(b.position, a.position, null))), 
          IF(d.position, b.position, IF(c.position, a.position, null)), 
          IF(d.position, a.position, null);

... etc, you'll get the pattern. BTW, it does not hurt to foresee more levels than needed. This last query will also return the correct result when you only have direct children, like in your sample data.

Daniel E. · Accepted Answer · 2018-02-27 15:04:17Z

0

With your expected result

SELECT * FROM myTable ORDER BY parentId, id , position

Else you can use COALESCE :

SELECT * FROM myTable
ORDER BY FIELD(COALESCE(parentId,'0'),id, position

answered Feb 27, 2018 at 15:04

Daniel E.

2,4811 gold badge16 silver badges25 bronze badges

Comments

Mark · Accepted Answer · 2018-02-27 15:13:34Z

0

It is not super clear, but this gives you what you expect

SELECT * FROM myTable ORDER BY parentId, position, IFNULL(parentId, id);

answered Feb 27, 2018 at 15:13

Mark

4183 silver badges12 bronze badges

Collectives™ on Stack Overflow

Mysql sorting by multiple columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related