Smarter sorting of values from an SQL database

Posted on Feb 13, 2011 in IT, Web Dev | 2 comments

Most sites that contain any significant amount of information manage that info with a database. That’s as it should be, because it’s smarter to write a script to handle how all that data is retrieved and output than to code it all by hand.  Then if you should need to change how that data is presented, you can change the script once instead of editing a few, a dozen, or even thousands of entries manually. Databases also make searching for specific data possible (or at least vastly easier).

Great, we’re in agreement that data should be stored and retrieved with a database. You’ve even got your scripts written so that your data appears in the order you want it on the page. For example, say you’ve got a page of products that includes name, price, and an company-specific product ID, like this:

Sorting a table of data like this one by product ID isn’t a likely scenario for customers; they’re more interested in a product’s name, description, price, etc. So let’s assume the audience for such a page is internal to the company, say for doing data-entry or review where sorting by ID might be useful. Assume further that these product IDs have a meaning where:

• GID – the first three letters represent the product type: “Book ID”, “Game ID”, or “Video ID”;
• 08 – the second two digits represent the year the product was added to the catalog: “08″ for 2008, etc.; and
• 214 – the last three digits are an auto-incrementing number to differentiate products.

This may all sound contrived, but I’ve worked with a company that uses product ID codes similar to this. That’s a lot of background, but with all this in mind, let’s look at how to sort values by year added. The year value is in the ID column, so let’s sort by ID..

By default, adding an ORDER BY ‘id’ clause to the SQL statement would sort our example table like this:

SELECT * FROM 'products' ORDER BY 'id'

Well, that worked sorta right. SQL did what it was supposed to do, which is sort by ID. But because the database is structured with the item type built into the ID, it makes sorting the items by the year component (or the other information buried within the full ID string) difficult. Yes, the database should have been built with a proper structure that separates information like “item type” and “date added” from the unique ID. But all arguments about database design aside, sometimes you run into situations like this when you inherit a project. The question is then: how can you sort by a value within a column’s data?

The answer lies with SQL’s mid() function. With it, you can select a portion of a column’s value to work with—in our example, sorting. So to sort by just the year portion of the ID, we can adjust our query like this:

SELECT * FROM 'products' ORDER BY mid('id',4,2)

This query manipulates the ID values before passing them to the ORDER BY clause. mid() takes a value (in this case, the text in the ID column), a start position (the character position within the string, with the first character being zero), and the number of characters to return. Additionally, SQL offers similar string functions like left() and right() to work with text at the  beginning or end of the string. By combining these functions as needed you can isolate just the relevant portions of the data you want to work with. These SQL functions allow you do get done what needs to get done even if the database wasn’t set up as efficiently as you might like.

(It’s worth noting, too, that sorting by string values like this example’s ID column is done so case-insensitively. That mean that an entry with ID “bid10-028″ would sort right before “BID10-029″. If you want to sort taking case sensitivity into account, add the keyword “binary” to your search clause, like so: ORDER BY BINARY ‘id’ )

Related: If you liked this, check out:

1. A visual aid for common database operations My database work these days as a web developer and...
2. Why no flags, OWA? First off: OWA is Outlook Web Access, the web-based version...
3. Another reason to love WordPress My adoration for WordPress, the open-source blogging and website tool,...
4. Adobe's PDF-generation (and customer service-) FAIL Recently I worked on a problem at my job whose...
5. How not to sell $10,000 support contracts We've all bought things online: books, sweaters, music, electronics, whatever....