Thanks for all your responses guys. I'm forwarding these on to the guy as I get them.
Bear in mind that I think a sort column is much easier too. At some point you HAVE to have SOME data on which to sort. Might as well store it in the database and save yourself headaches later on. <!----------------//------ andy matthews web developer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --------------//---------> -----Original Message----- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 01, 2006 11:24 AM To: CF-Talk Subject: Re: Ranking SQL records 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 etc then 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:233781 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
