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
Comments
0 comments
Please sign in to leave a comment.