CODEWITHSUNDEEP
sqlsortingcolumnsorting
KloppForKop
KloppForKop

Reputation: 114

How to Sort records in SQL like in a parent child order

I have a table with columns "ItemId" and "ParentItemId". I want the results to be sorted in Parent-Child Order. with this, there are other columns on which the data needs to be sorted. I want the data to be sorted based on the "itemType" First.

For Eg.

 AutoId | itemId | parentItemId | itemType
   1        1       0               3
   2        2       null            4
   3        3       0               6
   4        4       null            5
   5        5        1              9
   6        6        2              9
   7        7        3              9
   8        8        4              9
   9        9        0              2
   10       10       0              1

Now I want the results to be drawn like in the below format

 AutoId | itemId | parentItemId | itemType
   10       10        0              1
   9        9         0              2
   1        1         0              3
   5        5         1              9
   2        2        null            4
   6        6         2              9
   4        4        null            5
   8        8         4              9
   3        3         0              6 
   7        7         3              9

Is there a way i can sort the records like this?

Any Help would be appreciated. Thank you.

Upvotes: 1

Views: 319

Answers (3)

Jitendra Gupta
Jitendra Gupta

Reputation: 824

Taken from @hkutluay answer for SQL Server:

select * from table1 order by ISNULL(parentItemID, ItemID), ItemID

Upvotes: 0

hkutluay
hkutluay

Reputation: 6944

For MySql use ifnull for parentItemId field Like

select *
from table
order by IFNULL(parentItemId, itemId), itemId

for oracle

select *
from table
order by NVL(parentItemId, itemId), itemId

Upvotes: 1

Turophile
Turophile

Reputation: 3405

You can do this:

select *
from table1
order by coalesce(parentitemid,itemid), itemid

Example: http://sqlfiddle.com/#!9/32e58e/2

Upvotes: 2

Related Questions