Isis Isis - 6 months ago 12
SQL Question

Order row selection by multiple columns

I have a database

id | parentid | name
1 | 0 | CatOne
2 | 0 | CatTwo
3 | 0 | CatThree
4 | 1 | SubCatOne
5 | 1 | SubCatOne2
6 | 3 | SubCatThree


How I can select this cats Order By
id
,
parentid
? That is

CatOne 1
--SubCatOne 4
--SubCatOne2 5
CatTwo 2
CatThree 3
--SubCatThree 6

Answer

This should do it... with exception of a double dash "--" prefix to the name...

SELECT 
      t1.name,
      t1.id
   FROM 
      Table1 t1
   ORDER BY 
      case when t1.parentID = 0 then t1.ID else t1.ParentID end,
      case when t1.parentID = 0 then '1' else '2' end,
      t1.id

The order by FIRST case/when puts all the items that ARE the top level, or at the secondary level by the primary level's ID. So trying to use a parent * 1000 sample hack offered won't be an issue if you have over 1000 entries. The SECOND case/when will then force when the parent ID = 0 to the TOP of its grouped list and all its subsidiary entries UNDER it, but before the next parent ID.

however, if you DO want the double dash, change to

SELECT 
      if( t1.ParentID = 0, '', '--' ) + t1.name name,
     <rest of query is the same>