Ordering by Hierarchy

You can order hierarchical data (catalogs) by hierarchy.

Example:

SELECT
   Catalog.Contractors.Description,
   Catalog.Contractors.FullName

ORDER BY
   Catalog.Contractors.Name Hierarchy

Query result:

Name	FullName
Customers
Pavilion 45 in wholesale market	"Krasina" (Individual)
"Budenovski" trade house	"Budenovski trade house", Ltd
Jeans factory	"Jeans", Ltd
Clothing fair	"Clothing fair", Ltd
Vendors
Promstroibank
Bavaria China
"Zarya" knitwear factory
Jeans factory	"Jeans", Ltd

You can only sort hierarchically by field, but not by some of their operations: <Order field> must contain a reference to the source table field (the query data source).

It is important to understand, that it makes sense to order by hierarchy only if the catalog table itself is defined as the source, and not some other table containing a reference to the catalog only.

Example:

SELECT
   Invoice.Contractor.Name,
   Invoice.Number,
   Invoice.Amount

FROM
   Document.ExpInvoice AS Invoice

ORDER BY
   Invoice.Contractor HIERARCHY

In this example, hierarchical ordering is not possible, since there is no reference to the catalog, and groups from this catalog are not included in the query result.

To order by hierarchy, you must arrange for a reference to the catalog.

Example:

SELECT
   Contractor.Name,
   Invoice.Number,
   Invoice.Amount

FROM
   Catalog.Contractors AS Contractor

LEFT JOIN
   Document.ExpInvoice AS Invoice
BY
   Invoice.Contractor = Contractor.Reference

ORDER BY
   Contractor.Name Hierarchy

Query result:

Name	Number	Amount
Customers
Pavilion 45 in wholesale market	00007	6 000
Pavilion 45 in wholesale market	00006	11 500
Pavilion 45 in wholesale market	00016	80 000,23
"Budenovski" trade house	00004	1 500
"Budenovski" trade house	00003	52 500
"Budenovski" trade house	00002	16 000
"Budenovski" trade house	00009	555
"Budenovski" trade house	00015
"Budenovski" trade house	00014	16 500
"Budenovski" trade house	00005	1 500
Jeans factory
Clothing fair	00001	54 000
Vendors
Promstroibank
Bavaria China	00013	12 000,22
"Zarya" knitwear factory	00012	4 500
Jeans factory