CODEWITHSUNDEEP
sqlsql-serversorting
aseolin
aseolin

Reputation: 1200

Sort query by field related to another field

I have a table in my SQL Server database called dayorder, with the fields:

dayorder_cod (autoinc id),
dayorder_number (order number),
dayorder_sequence (order sequence)
dayorder_parent (parent order, can be null).

So I can have, for example, orders 100, 200, 998 and 999, with 100 being the parent of 998 and 999.

I want to sort the result as follows: 100, 998, 999, 200, i.e., the children of an order come just below it´s parent.

Upvotes: 0

Views: 35

Answers (2)

SqlZim
SqlZim

Reputation: 38043

No join needed:

if dayorder_parent references dayorder_number, then:

select d.*
from dayorder d
order by 
  isnull(d.dayorder_parent,d.dayorder_number)
  , d.dayorder_number

if dayorder_parent references dayorder_cod, then:

select d.*
from dayorder d
order by 
  isnull(d.dayorder_parent,d.dayorder_cod)
  , d.dayorder_cod

if dayorder_sequence is important to the order

select d.*
from dayorder d
order by 
  isnull(d.dayorder_parent,d.dayorder_cod)
  , d.dayorder_sequence

How it works: the query results are ordered by the dayorder_parent, but the initial row for each order will have a value of NULL for dayorder_parent.

Using isnull(d.dayorder_parent,d.dayorder_number) we can say that when an order doesn't have a parent, use its dayorder_number instead. Children of 100 get grouped with 100, and each group is further ordered by their individual dayorder_number.

note: you can use coalesce() instead of isnull() to get the same results. I tend to use coalesce() only when I need to get the first non-null value from a set of 3 or more values, as isnull() only supports two parameters.

Upvotes: 4

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

Try this:

SELECT t1.*
FROM dayorder AS t1
LEFT JOIN dayorder AS t2 ON t2.parent = t1.cod
ORDER BY COALESCE(t2.parent, t1.cod), t1.cod

Edit:

In case sequence field determines order in your table, then you can use:

SELECT t1.*
FROM dayorder AS t1
LEFT JOIN dayorder AS t2 ON t2.parent = t1.cod
ORDER BY COALESCE(t2.sequence, t1.sequence), t1.sequence

Upvotes: 3

Related Questions