MySQL order by id but prioritize an empty value of a certain column

Question

Mainly, I want to order the ids in descending direction but also, how can I sort parent product/items (the ones with empty ean) first and their child rows below them?

Here's an example data from the database:

| id   | ean           | name                                                        |
| 1005 | 4064722314889 | Camera Protector Series 15T - Model: 15T - Qty: 2           |
| 1004 | 4064722314896 | Camera Protector Series 15T - Model: 15T Pro - Qty: 2       |
| 1003 |               | Camera Protector Series 15T                                 |
| 1002 | 4064722314162 | Smartwatch Case Series GT6 Pro - Color: Black - Size: 46 mm |
| 1001 |               | Smartwatch Case Series GT6 Pro                              |

and so on...

Now, when I query it, I want to group it by id (desc) but also make sure to prioritize the row with an empty ean. Here's the desired result

| id   | ean           | name                                                        |
| 1003 |               | Camera Protector Series 15T                                 |
| 1005 | 4064722314889 | Camera Protector Series 15T - Model: 15T - Qty: 2           |
| 1004 | 4064722314896 | Camera Protector Series 15T - Model: 15T Pro - Qty: 2       |
| 1001 |               | Smartwatch Case Series GT6 Pro                              |
| 1002 | 4064722314162 | Smartwatch Case Series GT6 Pro - Color: Black - Size: 46 mm |

and so on...

Thanks in advance!

I don't understand your desired result. You have two rows with empty ean, but only one of them is prioritized. — Barmar
– Barmar, Commented Oct 28 at 15:34
Is there some other column that indicates the parent-child relationship? — Barmar
– Barmar, Commented Oct 28 at 15:38
Here's a solution: dbfiddle.uk/bXhilvZV I can't post it as an answer since other users have desperately moved to close this question, as they do nearly all questions on Stack Overflow. This site as really lost its way. — Bill Karwin
– Bill Karwin, Commented Oct 28 at 15:57
@bill-karwin I agree, at least we should know who is voting and why ! — masoudiofficial
– masoudiofficial, Commented Oct 28 at 16:31

d r · Accepted Answer · 2025-11-07 10:07:23Z

First you should get your parents and children together to have a common key to keep them together in the Order By close. In your sample data only column "name" (not a good choice for naming a column) could be used to match the children to the parent (it would be better to have some kind of common key).
Using your sample data - first fetch the parents in a cte and then join them to the children using complete parent's "name column" contained in the childrens' "name columns" (Inner Join ON() clause).
Having them together it is not to hard to do the ordering in a way you want - below is just one of the options to do it (using Case expression):

With
    parents AS
      ( Select * From tbl Where ean Is Null )
Select     t.*
From       tbl t
Inner Join parents p ON(Length(Replace(t.name, p.name, '')) = Length(t.name) - Length(p.name))
Order By   p.id Desc, Case When t.ean Is Null Then t.id + p.id Else t.id End Desc

id	ean	name
1003	null	Camera Protector Series 15T
1005	4064722314889	Camera Protector Series 15T - Model: 15T - Qty: 2
1004	4064722314896	Camera Protector Series 15T - Model: 15T Pro - Qty: 2
1001	null	Smartwatch Case Series GT6 Pro
1002	4064722314162	Smartwatch Case Series GT6 Pro - Color: Black - Size: 46 mm

fiddle

Collectives™ on Stack Overflow

MySQL order by id but prioritize an empty value of a certain column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related