Unusual But Logical SQL Sorting
September 13, 2006 Hey, Ted:
A physical file that I’ve loaded with data from another system has a department field that I would like to use for sorting. The department field contains a description, rather than a code. I need to sort the data on the description, but not in alphabetical order. That is, I want a certain department to appear first, then a certain department to appear next, and so on. The only way I know to pull this off is to create a little table that lists the departments and the order each one appears in the sort, but I do not like to clutter up the database with little one-use files. Do you have another idea?
Yes, I do. Let’s assume there are three departments–Accounting, Shipping, and Receiving–and you want to sort the data in that order. Alphabetical sorting will put Receiving ahead of Shipping, so that’s out.
Give the LOCATE function a try. In the first parameter, specify the name of the sort field. (I’ll assume it’s DEPT for this example.) The second parameter should contain a list of the departments. If the department field is fixed-length, be sure to pad each department name in the list-including the last one-with trailing blanks. Here’s an example:
select * from mydata order by locate(dept, 'Accounting Shipping Receiving ')
If you prefer, you can also use the POSITION and POSSTR functions.
select * from mydata order by posstr(dept, 'Accounting Shipping Receiving ') select * from mydata order by position(dept in 'Accounting Shipping Receiving ')
If DEPT is a variable-length field, you don’t have to pad with trailing blanks.
select * from mydata2 order by posstr(dept, 'AccountingShippingReceiving')
Or you can avoid the trailing blanks by dropping trailing blanks from the search argument.
select * from mydata order by locate(trim(dept), 'AccountingShippingReceiving')
Be aware that these functions return zero if the search argument is not in the list. Therefore, records for any departments you omit from the list will sort at the top of the returned data.