CODEWITHSUNDEEP
mysqlsqlsql-order-by
jitendra
jitendra

Reputation: 1458

SQL Order By query problem

I have a table with 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 want to fetch the data in the following order: 

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

What I mean is ORDERing has to occur by Username but records with same parent must show together and just below the record for the parent.

I am using MySQL.

Upvotes: 4

Views: 201

Answers (6)

Andriy M
Andriy M

Reputation: 77677

Something like this (not necessarily exactly like this):

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

I would actually try to replace '.' with a character that is unlikely to appear in the names.

Upvotes: 1

red-X
red-X

Reputation: 5128

try this new answer:

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

it concatenated the two strings so it should order like this:

abc
abcmno
abcpqr
def
defqfx
xyz

Upvotes: 2

Karl
Karl

Reputation: 3372

Other in Oracle I have found it necessary to write Stored Proc to do hierarchical ordering. Some RDBMSes offer CTE (common table expressions) which can acheive the same result. I am not familiar with mySql and don't know what support it has but the following may help.

Wikipedia article

mySql specific

Also Google "Joe Celko Trees and Hierarchies"

Upvotes: 0

BugFinder
BugFinder

Reputation: 17858

The biggest problem you have is you want 2 rather different aspects. You want it in username order, but if there is a matching parent you want to group it.

The most obvious would be

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

But that still sorts it by parent first.

Im sure its possible to get exactly what you wanted, but, I havent had the caffine yet to work out an easy full solution.

Upvotes: 0

heximal
heximal

Reputation: 10517

it's not a problem at all? catch it:

select * from mytable order by parent, name

Upvotes: 0

Vineet1982
Vineet1982

Reputation: 7918

Use the below query:

Select * from table_name
    Order by
        name, parent;

Upvotes: 0

Related Questions