Arranging query output with multiple ORDER BY 1

[JRSofty]

Here is the table I have

uid, pid, dis_order, title

uid = Unique ID
pid = Parent ID
dis_order = Display Order
title = title

Ok so I need to display this table in such a way that
1. ordered by display order
2. children are under their parents
3. children are also in their display order

I have this SQL that will get the children under their parents

SELECT * FROM `mytable` ORDER BY IF(`pid` = 0, `id`*100, `pid`*100+`id`)

but I can't seem to get everyone (including the parents) into display order.

Anyone got an idea?

JRSofty

http://jrsofty.stinkbugonline.com

 

[r937]

ORDER 
    BY case when pid = 0 then id else pid end
     , case when pid = 0 then  0 else  id end

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[JRSofty]

Ok that works like my IF statement but it doesn't get them into dis_order (display order) as well unless they all have sequential ids. In this case they might not have sequential ids where id=1 the dis_order might be 2 so that id=1 need be the second in the list.





JRSofty

http://jrsofty.stinkbugonline.com

 

[r937]

sorry, you're right, i forgot about the dis_order

select c.uid 
     , c.pid
     , c.dis_order
     , c.title
  from mytable as c
left outer
  join mytable as p
    on c.pic = p.uid
order
    by coalesce(p.dis_order,c.dis_order)
     , c.dis_order

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[JRSofty]

Brilliant,

Thanks for the help.

JRSofty

http://jrsofty.stinkbugonline.com