How about a SELECT TOP 1 and ORDER DESC ? ----- Original Message ----- From: "Jason Miller" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, February 12, 2003 5:39 PM Subject: Changing Order By - designating specific value to return first
> Hello list - I have a query that specifys a specific order - I need to > take the last value in the order by statement and alter that... for > example - > Query is > <cfquery name="GetModuleFeatures" datasource="#dsndata#"> > SELECT mf.iModule, mf.iApplication, mf.sModuleFeature, > mf.sFeatureIndent, mf.sFeatureSubHeading, mf.iModuleID, mf.LTC_Related, > mf.Reh_Related, mf.Inp_Related, mf.Out_Related, > am.smodulename > FROM ModuleFeatures mf, applicationmodules am > WHERE mf.imodule = am.iapplicationmoduleID AND #ProviderPage# = 1 AND > iApplication = #ApplicationPage# > ORDER BY iApplication, iModule, sFeatureSubHeading > </cfquery> > > THe ORDER BY iApplication, iModule is exactly as i need. > > Now - the issue is sFeatureSubHeading - If I sort by any logical order > it is incorrect - values returned as of now are as expected sample > output is: > > Distribution > General Features > Materials Management Inventory > Purchase Order Production etc etc etc > > What I would like is simple force if so General Features displays first > - then the others like so > > General Features > Distribution > Materials Management Inventory > Purchase Order Production etc etc etc > > I don't care really about the others although keeping the balance would > be good in alphabetical. > Typically I would give up and add a sort order field - which I don't > have... Is there another way to do it other than add a new column for no > reason to force General Features above all else? > > The query works great and is the core query for 16 active pages so I > don't want to have to modify it too much. > THanks! > jay > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4