SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Sunday, April 22, 2007

Outline number sorting

SQL Apprentice Question
Is there a way to in SQL to sort outline numbers to retain the
"natural order" state?

E.g.:


1.1
1.2
1.3
1.10


Shows up now as:
1.1
1.10
1.2
1.3


Is there any way to use recursive functionality to check for longer
outline numbers (i.e., like 1.1.1.1.1)?




Celko Answers
>> Is there a way to in SQL to sort outline numbers to retain the "natural order" state? <<


I use this system in my books and other things a lot. My solution is
to pad each section with leading zeros and hope I never have more than
99 headings.

00.00.
01.00.
01.01
01.01.02.
etc.


You enforce this with LIKE predicates and ORs in the DDL rather than
trying to do it in the DML.

No comments: