RE: Is there a way to do an arbitrary sort order on a specific cell?
Have you thought about building it up as an associate array first then sort based on the struct key? Then convert it to a query later? -Original Message- From: Les Mizzell [mailto:lesm...@bellsouth.net] Sent: Saturday, 15 May 2010 8:43 AM To: cf-talk Subject: Re: Is there a way to do an arbitrary sort order on a specific cell? Charlie Griefer wrote: > case statement in the ORDER BY should work. > > See http://www.devx.com/tips/Tip/17288 Checking my syntax, but doesn't seem to work for a query of queries.. Like: SELECT tHEAD, sid FROM hasMEDIA ORDER by ( Case thisHEAD WHEN 'Feature Article' THEN 1 WHEN 'Quoatable' THEN 2 WHEN 'On the Money' THEN 3 WHEN 'In the News' THEN 4 WHEN 'Innovation' THEN 5 WHEN 'About Us' THEN 6 WHEN 'Border Bound' THEN 7 WHEN 'Quick Facts' THEN 8 ELSE 100 END ) ERROR: Query Of Queries syntax error. Encountered "(. Incorrect ORDER BY column reference [(]. Only simple column reference, alias name, and integer column id are allowed. __ Information from ESET NOD32 Antivirus, version of virus signature database 5115 (20100514) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333722 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Is there a way to do an arbitrary sort order on a specific cell?
> Well .. either way, you will have to change to something if > the CASE values are not static. Be it a table, or a SQL > query. ... or your CF code. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333718 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Is there a way to do an arbitrary sort order on a specific cell?
> In theory, I could add a new database table to the site in > question, and populate it every X number of hours on a schedule, but > that's not exactly how this is set up to function. Well .. either way, you will have to change to something if the CASE values are not static. Be it a table, or a SQL query. However, if your base query is _not_ a database query, then you do not have many options, AFAIK. You are limited by the capabilities of QoQ's. It is either, add a sortNumber column and populate it row-by-row, or use a hack to simulate an OUTER join in QoQ's (tres ugly). ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333717 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Is there a way to do an arbitrary sort order on a specific cell?
> If you are using a database query as the source of this information, add a > CASE statement to your base database query to generate a column called > "SortNumber". Then use that column for ordering. Alternatively, you could > add a SortNumber column to your base table. I wish it were that easy. The information is coming from a number of different sources - RSS feeds, a database or two - and being consolidated down into a single source In theory, I could add a new database table to the site in question, and populate it every X number of hours on a schedule, but that's not exactly how this is set up to function. It's something I'm looking into though. Thanks! __ Information from ESET NOD32 Antivirus, version of virus signature database 5115 (20100514) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333716 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Is there a way to do an arbitrary sort order on a specific cell?
> Checking my syntax, but doesn't seem to work for a query of > queries.. QoQ's are very limited. They do not support CASE statements, AFAIK. If you are using a database query as the source of this information, add a CASE statement to your base database query to generate a column called "SortNumber". Then use that column for ordering. Alternatively, you could add a SortNumber column to your base table. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333715 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Is there a way to do an arbitrary sort order on a specific cell?
Charlie Griefer wrote: > case statement in the ORDER BY should work. > > See http://www.devx.com/tips/Tip/17288 Checking my syntax, but doesn't seem to work for a query of queries.. Like: SELECT tHEAD, sid FROM hasMEDIA ORDER by ( Case thisHEAD WHEN 'Feature Article' THEN 1 WHEN 'Quoatable' THEN 2 WHEN 'On the Money' THEN 3 WHEN 'In the News' THEN 4 WHEN 'Innovation' THEN 5 WHEN 'About Us' THEN 6 WHEN 'Border Bound' THEN 7 WHEN 'Quick Facts' THEN 8 ELSE 100 END ) ERROR: Query Of Queries syntax error. Encountered "(. Incorrect ORDER BY column reference [(]. Only simple column reference, alias name, and integer column id are allowed. __ Information from ESET NOD32 Antivirus, version of virus signature database 5115 (20100514) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333713 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Is there a way to do an arbitrary sort order on a specific cell?
> Is there a way to do an arbitrary sort order on a specific > cell? What do you mean by "cell"? A query, grid, etcetera ...? In general terms, you could assign a "sort number" to each color (red=1, blue=2, ecetera). Then sort by that value. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333703 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Is there a way to do an arbitrary sort order on a specific cell?
case statement in the ORDER BY should work. See http://www.devx.com/tips/Tip/17288 On Fri, May 14, 2010 at 9:24 AM, Les Mizzell wrote: > > Is there a way to do an arbitrary sort order on a specific cell? > > For example, let's say I have a cell "colour". > Possible values: > 1. blue > 2. cyan > 3. green > 4. orange > 5. red > 6. silver > > I want to sort on this cell, BUT, I want the order to show all > > red first > blue second > green third > ... blah ... > > > __ Information from ESET NOD32 Antivirus, version of virus > signature database 5115 (20100514) __ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333702 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Is there a way to do an arbitrary sort order on a specific cell?
Is there a way to do an arbitrary sort order on a specific cell? For example, let's say I have a cell "colour". Possible values: 1. blue 2. cyan 3. green 4. orange 5. red 6. silver I want to sort on this cell, BUT, I want the order to show all red first blue second green third ... blah ... __ Information from ESET NOD32 Antivirus, version of virus signature database 5115 (20100514) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333700 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
