Unusual But Logical SQL Sorting

Published: 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?

--Ross

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.

--Ted