Sorting SITE_ID values in the correct order

The article discusses an issue with the ordering of SITE_ID values in Geobank, where names with unique numbers might not appear in a logical sequence when sorted. For example, a list like "TEST SITE 1, TEST SITE 2, TEST SITE 3, TEST SITE 10" may not sort correctly due to the database's character field sorting. To address this, a query is suggested: `SELECT * FROM GB_SITE WHERE PROJECT = 'Default Values' ORDER BY len(SITE_ID), SITE_ID`. This query orders the SITE_ID values by length first, then by the SITE_ID itself, ensuring a more human-readable order.

In many cases where the SITE_ID values are the same in name, but they have unique numbers we might see that the ordering of the names could be incorrect in Geobank.

Let's say you have:

TEST SITE 1

TEST SITE 2

TEST SITE 3

TEST SITE 4

TEST SITE 5

TEST SITE 9

TEST SITE 10

TEST SITE 11

TEST SITE 20

For us a people this ordering seems logical, and we would like the values displayed as such.

However, for a database this ordering does not make sense when simply ordering on the SITE_ID values.

So, often times when we see the data it could look like this:

This order when sorting character fields is the correct way to sort the data, but it would be a nightmare to work with.

We can however use the following query to correctly sort the data in a way that we would like to see.

SELECT *
FROM GB_SITE
WHERE PROJECT = 'Default Values'
order by len(SITE_ID), SITE_ID 

So, in the query where we are ordering our data the following should be replaced.

ORDER BY SITE_ID

Should be replaced with:

order by len(SITE_ID), SITE_ID 

This will then correctly order your SITE_ID values in a way that makes sense to the human eye.

Want to learn more?

Online Help Manuals - Click here for the latest version

Learning Management System - Click here to login or here to request access