SQL query by query

Question

I have a table with the following dataset

id name parent ------------------ 1 xyz NULL 2 def NULL 3 mno abc 4 abc NULL 5 pqr abc 6 qfx def 

I would like to get the data in the following order:

 1 abc NULL 3 mno abc 5 pqr abc 2 def NULL 6 qfx def 4 xyz NULL 

I mean, ORDERing should be done by username, but records with the same parent should appear together and just below the record for the parent.

I am using MySQL.

Answer 1

try this new answer:

 SELECT * FROM table t ORDER BY CONCAT(parent, name) 

he combined the two lines, so he should do the following:

 abc abcmno abcpqr def defqfx xyz 

Answer 2

Something like this (not necessarily exactly like that):

 SELECT * FROM atable ORDER BY COALESCE(CONCAT(parent, '.'), '') + name 

I would try replacing '.' a character that is unlikely to appear in names.

Answer 3

Use the following query:

 Select * from table_name Order by name, parent; 

Answer 4

Is this not a problem at all? catch him:

 select * from mytable order by parent, name 

Answer 5

The biggest problem you face is that you want 2 quite different aspects. You want it in the order of the username, but if there is a matching parent, you want to group it.

The most obvious will be

 select username, parent from mytable order by parent,username group by parent 

But that still sorts it by parent.

I’m sure that you can get it exactly what you wanted, but I didn’t have coffee yet to work out an easy complete solution.

Answer 6

Other in Oracle, I found it necessary to write Stored Proc for hierarchical ordering. Some RDBMS offer CTEs (common table expressions) that can get the same result. I am not familiar with mySql and do not know what support it has, but the following may help.

Wikipedia article

mySql specific

Also Google "Joe Selco Trees and Hierarchies"