CODEWITHSUNDEEP
sql-server-2008
user1037747
user1037747

Reputation: 1373

case with SQL order by clause

I need help with the SQL ORDER BY clause. In my table below I want to order all the items so child items immediately follow their parent item. For example, in the below list, the last item has the ParentId value of 17 and follows an item whose ParentId is 23, but it should actually appear with the other ParentId=17 items.

enter image description here

In a similar way, if I later add a row with ParentId = 9, it should appear with items whose ParentId is 9 and not at the end of the list.

How should I sort my items to achieve the necessary output?

Upvotes: 1

Views: 160

Answers (1)

Andriy M
Andriy M

Reputation: 77737

Try the following query:

SELECT *
FROM yourtable
ORDER BY
  ISNULL(NULLIF(ParentId, 0), Id),
  Id  /* or maybe Level? */

Upvotes: 1

Related Questions