If you know them at the time of running your query and you, for some reason, cannot get the data as it exists in the database into a normalized state from which you could discern the order, then just use a case statement. I had a requirement on a report that a user be able to enter a list of company names in the order they wanted the invoices displayed (not to get into too much detail on a very stupid requirement), but it was basically a requirement to do exactly this. There was no way to get an alpha or a numeric rank associated with the records for the sort. So I did it this way and it worked just fine. (pseudo-code below)
case when business_title = 'CEO' then 1 when business_title = 'CIO' then 2 when business_title = 'VP-Sales' then 3 when business_title = 'VP-Ops' then 4 when business_title = 'Mail Clerk' then 5 when business_title = 'Janitor' then 6 when business_title = 'Doorman' then 7 as rank_order then you can order by rank_order asc or desc - whatever you need to accomplish. --Ferg Ken Ferguson wrote: > Do you mean you'd like to sort on a column such as business_title in a > non-alpha sort? > > eg, CEO, CIO, VP-Sales, VP-Ops, Mail Clerk, Janitor, Doorman (in that > order even though a normal order by clause won't give you that?) > > Is that what you're saying and if it is, do you know the list and the > order in which you want them beforehand? > > --Ferg > > Michael Traher wrote: > >> well a sort has to be based on some attribute of each record, either a >> number you assign as in your example of seniority ( a number could be >> assigned to job titles or maybe have a grade column) or as in your second >> example of photos being added to a gallery it could be on an existing >> column. For the photos what order do you want? could be chronological - >> date_added column or by the title of the photo perhaps? >> >> unless I miss the point entirely (which is quite possible :-) you have to >> decide the order and based it on one of the columns in the table. >> >> On 3/1/06, Andy Matthews <[EMAIL PROTECTED]> wrote: >> >> >>> Asking this question for a friend... >>> ----------------------------------- >>> I'm looking for a good way to pull entries from a database based on "rank" >>> (an arbitrary sort order). >>> >>> For example: A business' staff directory listed by seniority or position >>> (CEO at top) rather than default or alphabetical sort >>> OR: rearranging the display order of photos in a gallery >>> etc. >>> >>> I know I could add a field called 'sort' and assign a numerical value to >>> that field. But redefining the order could require modifying that field on >>> many records (especially in a photo gallery, where photos are being added >>> and removed). And if you wanted to add one between '5' and '6' you would >>> have to use something like '5.5' (then '5.7' to insert one between '5.5' >>> and >>> '6') which could also get ugly. >>> >>> There has to be a 'professional' simple solution to this, since it seems >>> like a very commonplace requirement. >>> ------------------------------- >>> >>> >>> >> -- >> Mike T >> Blog http://www.socialpoints.com/ >> >> >> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233772 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
