Ordering Technique in SQL:

We use ‘Order By’ function in SQL many times. Let's assume there are three Pay Codes—Personal Holiday, Vacation and Sick--and we want to sort the data in that order. Alphabetical sorting of departments will put Sick ahead of Vacation, so that's out.

This specific ordering can be accomplished by using the function ‘LOCATE’. In the first parameter, specify the name of the sort field. (I'll assume it's PAYCODE for this example.) The second parameter should contain a list of the Pay Codes. If the Pay Code field is fixed-length, be sure to pad each Pay Code name in the list-including the last one-with trailing blanks. Here's an example:

select * from PAYTABLE
order by locate(PAYCODE, 'PersonalHoliday Vacation Sick ')

This would sort the Pay Code in the requested order.