Tracker Ten Database Sort Order

Database Sort Order

Records in your database can be arranged in numerous ways. You might be concerned about the record sort order when performing various operations on your data like viewing search results, generating reports, or simply browsing through records.

Sometimes, the default sort order is simply the order the records were entered into the system. Internally each record may be assigned a sequence number that works as a key. However, this internal sequence number should never be relied on to ensure that your records can be retrieved in a certain order. Depending on how your database system works, the system may return existing records or add new records in a way that is most efficient for the system. If you would like to see your records in a specific order, you need to specify the sort mechanism.

In most relational database management systems (RDBMS) including SQL compliant databases there is no concept of a default sort order. Instead using an SQL query, you can specify sort order using the “ORDER BY” function. This function will let you order query results using any field in the database, either in ascending or descending order. Using a system like Tracker Ten, you simply need to select your desired sort order from the main menu. Programming is not necessary.

Database Sort by Time and Date

When you are database records it might be natural to view them based on a date in a record field. This date could be the date that the record was entered into the system, the date a sale was made, the date a record was last accessed, the date the record was last updated etc.

One thing to keep in mind when sorting by date is you need to make sure that the date field is defined as an appropriate time and date format. To a person, a date simply looks like a string of characters. But if you store a string of characters that represent a date directly in your database, the database will not understand how to sort the information. Instead, your database needs to know if it’s storing a date, so it can save it in the correct format. In fact, a database will store a string very differently then how it stores a date.

How do Databases Store Dates?

Databases have two main objectives when saving dates. First, they want to minimize the storage requirements of saving a date to memory. Second, they want to speed up queries involving dates. Query operations include date sorting. Internally dates are typically stored as integers. For example, on a Unix system a date is stored as a Unix epoch in a 64-bit integer. A Unix epoch is simply the number of seconds that have passed since January 1, 1970 UTC. The time zone is important to mention, as the number of seconds that have passed since January 1, 1970 will differ depending on the time zone that you are in.

The internal storage of a date is not to be confused by the date format. The date format is the date presented in a human readable from like “January 1, 2021” or “2021-01-01”. When your computer shows you a date it takes the internal integer format and it converts it to your desired date format.

Alphabetic Database Sort

An alphabetic database sort is exactly as it sounds. You ask your database to order the results alphabetically based on the contents of a particular field. Generally speaking, you want your database to ignore the case of the characters when requesting an alphabetic sort. This is because internally your database likely stores characters in ASCII format. In ASCII format each character is converted to an integer for storage (a computer can’t directly store a string of characters, it can only store numbers).

But in ASCII all capital letters are grouped together, and all small letters are grouped together. Therefore “aBC” may sort after “BCD” even though alphabetically “aBC” comes first. This is because the ASCII representation of “a” (61) is a higher number then the ASCII representation of “B” (42).

Another challenge you may run into when sorting alphabetically is if your database is encrypting data before it saves it. If you attempt to sort data while it’s encrypted, the results will not make any sense. Therefore, you want to make sure that your database system unencrypts data before it performs any sort operations on it. Typically, this process with happen automatically, so you don’t need to worry about it.

In a database like Tracker Ten, we automatically detect when it’s best to ignore case, and our database automatically handles case conversions for you.

Numeric Database Sort

You may opt to sort your database numerically using fields like “quantity” and “price”. Again, you need to make sure that your computer understands the format of the data. Often times prices will be stored with the “$” symbol. When sorting fields containing monetary values the computer needs to know to ignore currency symbols. The Tracker Ten database features a “money” field format just for this reason.

Secondary Sort

A secondary database sort is optionally applied after the initial sort. After an initial sort you may find clumps of items that appear together because they belong in exactly the same place on the list. In this case you can use a secondary sort to sort the similar items based on a different criterion. For example, if your initial sort is by name, the secondary sort might be by birthdate. In this case all people with the same name will appear together, but they will be sorted by their birthdate in the returned list.

More Sophisticated Sorts

There might be occasions where you need to perform sorting operations based on the relationship of a field to a particular data point. One example might be sorting by a distance, where you want to find the closest location to a particular address. In this case your database system may need to follow a number of steps to provide you with data sorted in the correct order. For example, first it may need to take an address and convert it to GPS coordinates. Then it may need to calculate the distance between GPS coordinates. Last it will have to sort records using this newly calculated distance. These types of complex sorting operations typically require custom programming. If needed we can provide this type of custom development. Please contact us for details.

Ascending and Descending

When sorting items in your database, you will need to select between an “Ascending” sort order and a “Descending” sort order. Ascending simply means that you start with the lowest value and end up with the highest value. Descending is the reverse. In a descending order you start with the highest value and you end up with the lowest value.

Database Sorting and Indexing

If you commonly sort data in your database using a particular sort order, it might make sense to setup an “index”. Using an index, the original order of records in your database stays the same. However, a new table is created that points to records in the original database in a desired sort order. You can think of an “index” like an old-style phone book, where you can quickly look up people alphabetically. Similarly, an alphabetic index on a field will simply be a list of pointers that let you quickly retrieve database records in an alphabetic order.

Database Sorting Techniques

Internally your database you use a variety of techniques to sort information including:

As an end user you do not need any detailed knowledge of these sorting algorithms. Your database will automatically select and implement the best algorithm when you request a sorting operation. If you notice that your information has not been sorted correctly, it’s most likely because the field type is not correctly specified in the database (for example you are storing a date as a string of characters instead of a date). It’s very unlikely that the internal sorting algorithm has failed.