Sorting PersonID's Numerically

From TNG_Wiki
Jump to navigation Jump to search

Tip

Executing the following query in MySQL :

SELECT personID, lastname, firstname 
FROM tng_people 
ORDER BY personID

Will output our results with the personID's sorted alphabetically as the field is defined as type alphanumeric:

I1 I10 I100 I2 I20 I21 I3 I4 . . etc

However, there may times where we want our results sorted in a numeric order (a.k.a natural sort) e.g.

I1 I2 I3 I4 I5 I6 I7 I8 I9 I10 I11...etc,

To sort naturally we can change the ORDER BY statement to :

SELECT personID, lastname, firstname 
FROM tng_people 
ORDER BY LENGTH(personID), personID

And our results are now sorted numerically by personID:

I1 I2 I3 I4 I5 I6 I7 I8 I9 I10


How Does it Work ?

The

ORDER BY LENGTH(personID), personID

statement works because the 'alpha' part of our personID field, which in this case is the default prefix 'I', is the same value and length for all records.

So our ORDER BY first sorts our personID's by length into groups using LENGTH(personId), which are then subsorted by personID to output the results in a natural order.


Note

As stated above this 'tip' will only work if the alpha prefix of the field to be sorted naturally is the same character or string of characters and length for all records.

This tip can also be used to sort the familyID's numerically from the tng_families table :

SELECT
familyID,
husband,
wife
FROM
tng_families
ORDER BY
LENGTH(familyID), familyID


Mark 01:40, 10 August 2013 (CDT)