it may be faster in some situations to do the below operation using a
temp table...
CREATE TABLE #tempsort(business_title varchar(255), sortorder int)
INSERT INTO #tempsort VALUES('CEO',1)
....etc etcthen do a join on the temp table to return your sorted results. whether or not this is faster probably depends on how much data is being returned...but I'm guessing there. On 3/1/06, Ken Ferguson <[EMAIL PROTECTED]> wrote: > 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:233777 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
