CODEWITHSUNDEEP
mysqlsortingselectparent-child
MajAfy
MajAfy

Reputation: 3097

Select records with sub value after parent record in mysql

I have this records in my table :

id   |   title     |  sub
-----+-------------+------
1    | Parent 1    | 0
2    | Parent 2    | 0
3    | Sub 1-1     | 1
4    | Parent 3    | 0
5    | Sub 1-2     | 1
6    | Sub 2-1     | 2
7    | Parent 4    | 0

Now I want select this records from my table with one query like this :

id   |   title     |  sub
-----+-------------+------
1    | Parent 1    | 0
3    | Sub 1-1     | 1
5    | Sub 1-2     | 1
2    | Parent 2    | 0
6    | Sub 2-1     | 2
4    | Parent 3    | 0
7    | Parent 4    | 0

I want to sort my records by Parent and then childs.

How can I do this in mySQL ?

UPDATE:

I use this query :

SELECT a.*, 
       CASE WHEN SUB = 0 THEN ID ELSE SUB END expression
  FROM category a
 ORDER BY CASE WHEN SUB = 0 THEN ID ELSE SUB END, ID

And my data is :

id  | title     | sub 
----+---------------+-------
1   | Parent 1  | 0
2   | Parent 2  | 0
3   | Sub 1-1   | 7
4   | Parent 3  | 0
5   | Sub 1-2   | 4
6   | Sub 2-1   | 2
7   | Parent 4  | 0

The result is :

id  | title     | sub   | expression
----+-----------+-------+-----------
1   | Parent 1  | 0     | 1
2   | Parent 2  | 0     | 2
6   | Sub 2-1   | 2     | 2
4   | Parent 3  | 0     | 4
5   | Sub 1-2   | 4     | 4
3   | Sub 1-1   | 7     | 7
7   | Parent 4  | 0     | 7

Upvotes: 1

Views: 242

Answers (2)

Mateus Schneiders
Mateus Schneiders

Reputation: 4903

If there wouldn't be more than 1 level, you could make an expression with CASE on your order by clause:

SELECT a.*, 
       CASE WHEN SUB = 0 THEN ID ELSE SUB END expression
  FROM test a
 ORDER BY CASE WHEN SUB = 0 THEN ID ELSE SUB END, SUB, TITLE

With that expression, it will always order by the parentId, which is the SUB column when populated or the ID column when it is not a child.

Would result:

ID  TITLE        SUB   EXPRESSION
1   Parent 1       0          1
3   Sub 1-1        1          1
5   Sub 1-2        1          1
2   Parent 2       0          2
6   Sub 2-1        2          2
4   Parent 3       0          4
7   Parent 4       0          7

It would still fail if any of the Child could be a parent of another record (Sub 1-2-1 for example).

Upvotes: 1

echo_Me
echo_Me

Reputation: 37233

try this

   SELECT * ,replace(replace(title,'Parent',''),'Sub','') as nums  
   FROM Table1
    order by nums

SQLFIDDLE DEMO HERE

would result

   ID   TITLE      SUB   NUMS
   1    Parent 1    0     1
   3    Sub 1-1     1     1-1
   5    Sub 1-2     1     1-2
   2    Parent 2    0     2
   6    Sub 2-1     2     2-1
   4    Parent 3    0     3
   7    Parent 4    0     4

EDIT.

due to your different result wish from your question . try this

SELECT * ,replace(title,'Item','') as nums  
FROM category
order by nums

DEMO SQLFIDDLE

Obs: be sure that Item is separated from digits .

Upvotes: 0

Related Questions