Re: Order By question
You'll need to create a computed column, called say isMatch, based perhaps on their ID. You can set isMatch to a bit based on if the id matches. 1 for yes or 0 for no. Then order by can be Order by isMatch ASC, last_name ASC On Wed, Jun 30, 2010 at 3:02 PM, Les Mizzell lesm...@bellsouth.net wrote: Not 100% sure how to do this... Need to return a list of folks ordered by last_name (no problem), EXCEPT for 2 of them. They need to be LAST regardless of their last names (problem)... Perhaps something with their 2 ID numbers? Ideas? __ Information from ESET NOD32 Antivirus, version of virus signature database 5241 (20100630) __ 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:334973 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Order By question
Something like... select last_name from table order by case ID when #ID1# then 0 when #ID2# then 0 else 1 end, last_name On Wed, Jun 30, 2010 at 2:02 PM, Les Mizzell lesm...@bellsouth.net wrote: Not 100% sure how to do this... Need to return a list of folks ordered by last_name (no problem), EXCEPT for 2 of them. They need to be LAST regardless of their last names (problem)... Perhaps something with their 2 ID numbers? Ideas? __ Information from ESET NOD32 Antivirus, version of virus signature database 5241 (20100630) __ 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:334974 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Order By question
Exclude them from the query, then append them with QueryAddRow() and QuerySetCell() -Original Message- From: Les Mizzell [mailto:lesm...@bellsouth.net] Sent: Wednesday, June 30, 2010 1:03 PM To: cf-talk Subject: Order By question Not 100% sure how to do this... Need to return a list of folks ordered by last_name (no problem), EXCEPT for 2 of them. They need to be LAST regardless of their last names (problem)... Perhaps something with their 2 ID numbers? Ideas? __ Information from ESET NOD32 Antivirus, version of virus signature database 5241 (20100630) __ 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:334977 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Order By question
I respectfully disagree with this approach. Doing it in the db will be much faster. On Wed, Jun 30, 2010 at 3:06 PM, Jon Sala jon.s...@albertahealthservices.ca wrote: Exclude them from the query, then append them with QueryAddRow() and QuerySetCell() -Original Message- From: Les Mizzell [mailto:lesm...@bellsouth.net] Sent: Wednesday, June 30, 2010 1:03 PM To: cf-talk Subject: Order By question Not 100% sure how to do this... Need to return a list of folks ordered by last_name (no problem), EXCEPT for 2 of them. They need to be LAST regardless of their last names (problem)... Perhaps something with their 2 ID numbers? Ideas? __ Information from ESET NOD32 Antivirus, version of virus signature database 5241 (20100630) __ 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:334979 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Order By question
Absolutely a consideration - it may be too heavy a cost - depending on factors not included in the question. In some cases however, there just is no replacement for the Query*() functions, but I concur this example isn't necessarily one of them. Speaking for myself, I appreciate being made aware of other techniques available on the platform, and using my own judgement as to the best tool for the job. Regards, -Original Message- From: Michael Grant [mailto:mgr...@modus.bz] Sent: Wednesday, June 30, 2010 1:40 PM To: cf-talk Subject: Re: Order By question I respectfully disagree with this approach. Doing it in the db will be much faster. On Wed, Jun 30, 2010 at 3:06 PM, Jon Sala jon.s...@albertahealthservices.ca wrote: Exclude them from the query, then append them with QueryAddRow() and QuerySetCell() -Original Message- From: Les Mizzell [mailto:lesm...@bellsouth.net] Sent: Wednesday, June 30, 2010 1:03 PM To: cf-talk Subject: Order By question Not 100% sure how to do this... Need to return a list of folks ordered by last_name (no problem), EXCEPT for 2 of them. They need to be LAST regardless of their last names (problem)... Perhaps something with their 2 ID numbers? Ideas? __ Information from ESET NOD32 Antivirus, version of virus signature database 5241 (20100630) __ 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:334981 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Order By question
I secont John's SQL. To expand explain further see my example. SELECT * FROM folks ORDER BY CASE WHEN last_name = 'Smith' OR last_name = 'Jones' THEN 0 ELSE last_name END Paul Alkema http://paulalkema.com/ -Original Message- From: John M Bliss [mailto:bliss.j...@gmail.com] Sent: Wednesday, June 30, 2010 3:09 PM To: cf-talk Subject: Re: Order By question Something like... select last_name from table order by case ID when #ID1# then 0 when #ID2# then 0 else 1 end, last_name On Wed, Jun 30, 2010 at 2:02 PM, Les Mizzell lesm...@bellsouth.net wrote: Not 100% sure how to do this... Need to return a list of folks ordered by last_name (no problem), EXCEPT for 2 of them. They need to be LAST regardless of their last names (problem)... Perhaps something with their 2 ID numbers? Ideas? __ Information from ESET NOD32 Antivirus, version of virus signature database 5241 (20100630) __ 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:334982 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Order By question
And by much faster he means by 100 milliseconds. Realistically, I would not consider the appending of two rows to a query to be a time consuming operation in CF. I would disagree with that approach too, but only on the grounds that is more work, more code, and more convoluted than a simple case statement in your order by. ORDER BY CASE WHEN id IN (x,y) THEN 1 ELSE 0 END, last_name Either way, it's good to know all the cat-skinning options CF gives you. ~Brad Original Message Subject: Re: Order By question From: Michael Grant mgr...@modus.bz Date: Wed, June 30, 2010 2:40 pm To: cf-talk cf-talk@houseoffusion.com I respectfully disagree with this approach. Doing it in the db will be much faster. ~| 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:334983 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Order By question
Let's hope no one has a last name starting with any of the following characters: space ! # $ % ' ( ) * + , - . / All of those fall alphabetically before 0. :) ~Brad Original Message Subject: RE: Order By question From: Paul Alkema paulalkemadesi...@gmail.com Date: Wed, June 30, 2010 3:06 pm To: cf-talk cf-talk@houseoffusion.com I secont John's SQL. To expand explain further see my example. SELECT * FROM folks ORDER BY CASE WHEN last_name = 'Smith' OR last_name = 'Jones' THEN 0 ELSE last_name END ~| 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:334985 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Order By question
Yeah *that's* why I didn't suggest your more terse ELSE last_name Yeah. That's the ticket.;-) On Wed, Jun 30, 2010 at 3:13 PM, b...@bradwood.com wrote: Let's hope no one has a last name starting with any of the following characters: space ! # $ % ' ( ) * + , - . / All of those fall alphabetically before 0. :) ~Brad Original Message Subject: RE: Order By question From: Paul Alkema paulalkemadesi...@gmail.com Date: Wed, June 30, 2010 3:06 pm To: cf-talk cf-talk@houseoffusion.com I secont John's SQL. To expand explain further see my example. SELECT * FROM folks ORDER BY CASE WHEN last_name = 'Smith' OR last_name = 'Jones' THEN 0 ELSE last_name END ~| 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:334986 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Order By question
lol. My list-of-names-that-will-screw-up-DBAs just got a little longer. ~Brad Original Message Subject: Re: Order By question From: John M Bliss bliss.j...@gmail.com Date: Wed, June 30, 2010 3:16 pm To: cf-talk cf-talk@houseoffusion.com Yeah *that's* why I didn't suggest your more terse ELSE last_name Yeah. That's the ticket. ;-) On Wed, Jun 30, 2010 at 3:13 PM, b...@bradwood.com wrote: Let's hope no one has a last name starting with any of the following characters: space ! # $ % ' ( ) * + , - . / All of those fall alphabetically before 0. :) ~Brad ~| 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:334992 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Order By question
Check out the article linked in http://developers.slashdot.org/story/10/06/17/2347257/Falsehoods-Programmers-Believe-About-Names for a good read on that. - Andrew. On 2010-06-30, at 16:34, b...@bradwood.com wrote: lol. My list-of-names-that-will-screw-up-DBAs just got a little longer. ~Brad Original Message Subject: Re: Order By question From: John M Bliss bliss.j...@gmail.com Date: Wed, June 30, 2010 3:16 pm To: cf-talk cf-talk@houseoffusion.com Yeah *that's* why I didn't suggest your more terse ELSE last_name Yeah. That's the ticket. ;-) On Wed, Jun 30, 2010 at 3:13 PM, b...@bradwood.com wrote: Let's hope no one has a last name starting with any of the following characters: space ! # $ % ' ( ) * + , - . / All of those fall alphabetically before 0. :) ~Brad ~| 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:334994 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Order By question
I respectfully disagree with this approach. Doing it in the db will be much faster. Not so sure: If the query has to be ordered by the name, it could use an internal index. If it has to be sorted by some artificial column, there is no index and the query could be much longer. So IMO this approach may be the best, especially if the two exceptions have to be added at the end of the query. ~| 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:334996 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Order By question
I love how almost every question asked on this list will eventually turn into some kind of debate. It's great. :) -Original Message- From: =?ISO-8859-1?Q?Claude_Schn=E9egans [mailto:schneeg...@interneti=71?= =?ISO-8859-1?Q?ue.com=3E?=] Sent: Wednesday, June 30, 2010 5:03 PM To: cf-talk Subject: Re: Order By question I respectfully disagree with this approach. Doing it in the db will be much faster. Not so sure: If the query has to be ordered by the name, it could use an internal index. If it has to be sorted by some artificial column, there is no index and the query could be much longer. So IMO this approach may be the best, especially if the two exceptions have to be added at the end of the query. ~| 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:334997 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Order By question
Works the charm: SELECT * FROM judges ORDER BY CASE WHEN judge_id IN (2589,7555) THEN 1 ELSE 0 END, judge_lname Thanks folks... __ Information from ESET NOD32 Antivirus, version of virus signature database 5241 (20100630) __ 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:334998 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Order By question
Paul, now's a good time to start a select * rant. :) Glad it works for you Les. ~Brad Original Message Subject: Re: Order By question From: Les Mizzell lesm...@bellsouth.net Date: Wed, June 30, 2010 4:18 pm To: cf-talk cf-talk@houseoffusion.com Works the charm: SELECT * FROM judges ORDER BY CASE WHEN judge_id IN (2589,7555) THEN 1 ELSE 0 END, judge_lname Thanks folks... ~| 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:334999 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Order By question
b...@bradwood.com wrote: Paul, now's a good time to start a select * rant. :) No - I SPECIFY which fields I want and cfueryparam the hell out of everything too. I just put select * here for brevity. The below is NOT the live query! SELECT * FROM judges ORDER BY CASE WHEN judge_id IN (2589,7555) THEN 1 ELSE 0 END, judge_lname __ Information from ESET NOD32 Antivirus, version of virus signature database 5241 (20100630) __ 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:335000 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Order By question
It's what happens when you get a bunch of uber intelligent people who have a lot of knowledge in the same room. Eric -Original Message- From: Paul Alkema [mailto:paulalkemadesi...@gmail.com] Sent: Wednesday, June 30, 2010 4:12 PM To: cf-talk Subject: RE: Order By question I love how almost every question asked on this list will eventually turn into some kind of debate. It's great. :) -Original Message- From: =?ISO-8859-1?Q?Claude_Schn=E9egans [mailto:schneeg...@interneti=71?= =?ISO-8859-1?Q?ue.com=3E?=] Sent: Wednesday, June 30, 2010 5:03 PM To: cf-talk Subject: Re: Order By question I respectfully disagree with this approach. Doing it in the db will be much faster. Not so sure: If the query has to be ordered by the name, it could use an internal index. If it has to be sorted by some artificial column, there is no index and the query could be much longer. So IMO this approach may be the best, especially if the two exceptions have to be added at the end of the query. ~| 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:335001 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Order By question
Paul, I hate to disagree with you but Mark A. Kruger, MCSE, CFG (402) 408-3733 ext 105 Skype: markakruger www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Paul Alkema [mailto:paulalkemadesi...@gmail.com] Sent: Wednesday, June 30, 2010 4:12 PM To: cf-talk Subject: RE: Order By question I love how almost every question asked on this list will eventually turn into some kind of debate. It's great. :) -Original Message- From: =?ISO-8859-1?Q?Claude_Schn=E9egans [mailto:schneeg...@interneti=71?= =?ISO-8859-1?Q?ue.com=3E?=] Sent: Wednesday, June 30, 2010 5:03 PM To: cf-talk Subject: Re: Order By question I respectfully disagree with this approach. Doing it in the db will be much faster. Not so sure: If the query has to be ordered by the name, it could use an internal index. If it has to be sorted by some artificial column, there is no index and the query could be much longer. So IMO this approach may be the best, especially if the two exceptions have to be added at the end of the query. ~| 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:335002 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Order By question
You might even consider a subquery to get those two id values: SELECT * FROM judges ORDER BY CASE WHEN judge_id IN ( SELECT id FROM users WHERE some criteria ) THEN 1 ELSE 0 END, judge_lname Then you wouldn't have to hard code them. andy -Original Message- From: Les Mizzell [mailto:lesm...@bellsouth.net] Sent: Wednesday, June 30, 2010 4:19 PM To: cf-talk Subject: Re: Order By question Works the charm: SELECT * FROM judges ORDER BY CASE WHEN judge_id IN (2589,7555) THEN 1 ELSE 0 END, judge_lname Thanks folks... __ Information from ESET NOD32 Antivirus, version of virus signature database 5241 (20100630) __ 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:335005 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Order by question
Hi Paul, You can make use of CONVERT (or CAST, I think). For example: The table to be sorted here has two columns, the ID and the values to be sorted: SELECT TOP 100 PERCENT tempID, CONVERT(varchar, NumValues) AS TextValues FROM dbo.tbl_temp ORDER BY CONVERT(varchar, NumValues) In your code it would be: cfquery name=get_PH datasource=xxx SELECT convert(varchar, hier_num) as TextValues, .hier_desc FROM hierarchy ORDER BY CONVERT(varchar, hier_num) /cfquery Hope this helps, Jenny -Original Message- From: Paul Ihrig [mailto:[EMAIL PROTECTED] Sent: 03 April 2008 21:38 To: CF-Talk Subject: Order by question if i have a bunch of numbers that i want to order like this in a drop dowm, how do i do it? the origional order by id was fine when the list was small.. but now it has grown. forcing it to look the way we want below is boggeling.. 1 101 101010 2 201 202 201010 201020 3 302 et... cfquery name=get_PH datasource=xxx SELECT hier_num, .hier_desc FROM hierarchy /cfquery if i order by hier_num it just does as expected 1 2 3 101 201 301 et as always i appreciate your help. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303221 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Order by question
Oh, and my solution is for MS SQL 2000 ... -Original Message- From: Jenny Gavin-Wear [mailto:[EMAIL PROTECTED] Sent: 12 April 2008 01:00 To: CF-Talk Subject: RE: Order by question Hi Paul, You can make use of CONVERT (or CAST, I think). For example: The table to be sorted here has two columns, the ID and the values to be sorted: SELECT TOP 100 PERCENT tempID, CONVERT(varchar, NumValues) AS TextValues FROM dbo.tbl_temp ORDER BY CONVERT(varchar, NumValues) In your code it would be: cfquery name=get_PH datasource=xxx SELECT convert(varchar, hier_num) as TextValues, .hier_desc FROM hierarchy ORDER BY CONVERT(varchar, hier_num) /cfquery Hope this helps, Jenny -Original Message- From: Paul Ihrig [mailto:[EMAIL PROTECTED] Sent: 03 April 2008 21:38 To: CF-Talk Subject: Order by question if i have a bunch of numbers that i want to order like this in a drop dowm, how do i do it? the origional order by id was fine when the list was small.. but now it has grown. forcing it to look the way we want below is boggeling.. 1 101 101010 2 201 202 201010 201020 3 302 et... cfquery name=get_PH datasource=xxx SELECT hier_num, .hier_desc FROM hierarchy /cfquery if i order by hier_num it just does as expected 1 2 3 101 201 301 et as always i appreciate your help. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303222 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Order by question
Paul Ihrig wrote: 1 101 101010 2 201 202 201010 201020 3 302 This is an alphabetical order of numbers. Why the list is being order alphabetically I can not say without seeing some relevant code. But the solution is to tell your code to treat this data a numerical data not character data. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302658 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Order by question
Try this out. You need to sort by a text value of the hier_num select hier_num, hier_desc, convert(varchar(20),hier_num) hier_text from hiertbl order by hier_text Cheers, Kris On Thu, Apr 3, 2008 at 4:37 PM, Paul Ihrig [EMAIL PROTECTED] wrote: if i have a bunch of numbers that i want to order like this in a drop dowm, how do i do it? the origional order by id was fine when the list was small.. but now it has grown. forcing it to look the way we want below is boggeling.. 1 101 101010 2 201 202 201010 201020 3 302 et... cfquery name=get_PH datasource=xxx SELECT hier_num, .hier_desc FROM hierarchy /cfquery if i order by hier_num it just does as expected 1 2 3 101 201 301 et ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302659 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Order by question
If I understand right, he wants the alpha order, not the numeric order. To that end, I have no suggestions, db sql stuff are not my strong skills. On Thu, Apr 3, 2008 at 3:55 PM, Ian Skinner [EMAIL PROTECTED] wrote: Paul Ihrig wrote: 1 101 101010 2 201 202 201010 201020 3 302 This is an alphabetical order of numbers. Why the list is being order alphabetically I can not say without seeing some relevant code. But the solution is to tell your code to treat this data a numerical data not character data. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302660 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Order by question
it is not sorting this way... i Want it to sort this way On Thu, Apr 3, 2008 at 4:55 PM, Ian Skinner [EMAIL PROTECTED] wrote: Paul Ihrig wrote: 1 101 101010 2 201 202 201010 201020 3 302 This is an alphabetical order of numbers. Why the list is being order alphabetically I can not say without seeing some relevant code. But the solution is to tell your code to treat this data a numerical data not character data. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302662 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Order by question
Are you storing the numbers as text or as a number in the database? If you are doing an order by and the numbers are being stored as text in the database the numbers will be sorted like words and not numbers. On Thu, Apr 3, 2008 at 5:15 PM, Paul Ihrig [EMAIL PROTECTED] wrote: it is not sorting this way... i Want it to sort this way On Thu, Apr 3, 2008 at 4:55 PM, Ian Skinner [EMAIL PROTECTED] wrote: Paul Ihrig wrote: 1 101 101010 2 201 202 201010 201020 3 302 This is an alphabetical order of numbers. Why the list is being order alphabetically I can not say without seeing some relevant code. But the solution is to tell your code to treat this data a numerical data not character data. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302663 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Order by question
Kris. thats closer to what i an looking for. but not exact... it now does 1 1100105 1100101 10 1001 100101 100102 1002 100201 100202 1003 100301 100303 101 i would like 1 10 101 1001 100101 100102 1002 100201 100202 1003 100301 100303 1100105 1100101 shjt.. not even sure if this makes sense... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302664 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Order by question
Gerald they are nums in a num field they sort correctly if i wanted them sorted like a number. but powers that be want them sorted in a was to show hierarchy.. nm... i will tell them we can recreat the table with the order they want by hand... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302665 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Order by question
You can turn the result set into a list and then do a list sort and sort it alphabetically like so: ListSort(valuelist(get_PH.hier_num, ,), TextNoCase, ASC, ,) or this (will probably run faster) ListSort(valuelist(get_PH.hier_num, ,), Text, ASC, ,) And then loop over the list for your drop down. G On Thu, Apr 3, 2008 at 5:31 PM, Paul Ihrig [EMAIL PROTECTED] wrote: Gerald they are nums in a num field they sort correctly if i wanted them sorted like a number. but powers that be want them sorted in a was to show hierarchy.. nm... i will tell them we can recreat the table with the order they want by hand... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302666 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Order by question
Paul Ihrig wrote: Gerald they are nums in a num field they sort correctly if i wanted them sorted like a number. but powers that be want them sorted in a was to show hierarchy.. OK then reverse my original advice. Tell your system to sort them as characters rather then numbers. You should be able to do this with a simple CAST in your ORDER BY clause, if you are not using to simple a database management system. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302668 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Order by question
Paul... What DBMS are you using? In MySQL 5, sorting a numeric column apparently defaults to sorting alphabetically. CREATE TABLE `tmp` ( `c` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO tmp (c) VALUES (1); INSERT INTO tmp (c) VALUES (101); INSERT INTO tmp (c) VALUES (101010); INSERT INTO tmp (c) VALUES (2); INSERT INTO tmp (c) VALUES (201); INSERT INTO tmp (c) VALUES (201010); INSERT INTO tmp (c) VALUES (201020); SELECT * FROM `tmp` ORDER BY c ASC -Original Message- From: Paul Ihrig [mailto:[EMAIL PROTECTED] Sent: Thursday, April 03, 2008 4:31 PM To: CF-Talk Subject: Re: Order by question Gerald they are nums in a num field they sort correctly if i wanted them sorted like a number. but powers that be want them sorted in a was to show hierarchy.. nm... i will tell them we can recreat the table with the order they want by hand... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302670 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Order by question
the closest I could get was select * from foo order by rpad(col, 10, '0') But it does not the results you're asking. On Thu, Apr 3, 2008 at 4:49 PM, Andy Matthews [EMAIL PROTECTED] wrote: Paul... What DBMS are you using? In MySQL 5, sorting a numeric column apparently defaults to sorting alphabetically. CREATE TABLE `tmp` ( `c` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO tmp (c) VALUES (1); INSERT INTO tmp (c) VALUES (101); INSERT INTO tmp (c) VALUES (101010); INSERT INTO tmp (c) VALUES (2); INSERT INTO tmp (c) VALUES (201); INSERT INTO tmp (c) VALUES (201010); INSERT INTO tmp (c) VALUES (201020); SELECT * FROM `tmp` ORDER BY c ASC -Original Message- From: Paul Ihrig [mailto:[EMAIL PROTECTED] Sent: Thursday, April 03, 2008 4:31 PM To: CF-Talk Subject: Re: Order by question Gerald they are nums in a num field they sort correctly if i wanted them sorted like a number. but powers that be want them sorted in a was to show hierarchy.. nm... i will tell them we can recreat the table with the order they want by hand... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302671 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Order by question
I'm retarded...this is the query you want for MySQL: SELECT CONCAT( '0' + c ) AS value FROM tmp ORDER BY value The previous wasn't sorted alpha by default...I'm just an idiot. The above query though does return your desired result set: 1 101 101010 2 201 201010 201020 -Original Message- From: Andy Matthews [mailto:[EMAIL PROTECTED] Sent: Thursday, April 03, 2008 4:49 PM To: CF-Talk Subject: RE: Order by question Paul... What DBMS are you using? In MySQL 5, sorting a numeric column apparently defaults to sorting alphabetically. CREATE TABLE `tmp` ( `c` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO tmp (c) VALUES (1); INSERT INTO tmp (c) VALUES (101); INSERT INTO tmp (c) VALUES (101010); INSERT INTO tmp (c) VALUES (2); INSERT INTO tmp (c) VALUES (201); INSERT INTO tmp (c) VALUES (201010); INSERT INTO tmp (c) VALUES (201020); SELECT * FROM `tmp` ORDER BY c ASC -Original Message- From: Paul Ihrig [mailto:[EMAIL PROTECTED] Sent: Thursday, April 03, 2008 4:31 PM To: CF-Talk Subject: Re: Order by question Gerald they are nums in a num field they sort correctly if i wanted them sorted like a number. but powers that be want them sorted in a was to show hierarchy.. nm... i will tell them we can recreat the table with the order they want by hand... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302672 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Order by question
Opps too many quotes on the Listsort function ListSort(valuelist(get_PH.hier_num, ,), TextNoCase, ASC, ,) On Thu, Apr 3, 2008 at 5:45 PM, Gerald Guido [EMAIL PROTECTED] wrote: You can turn the result set into a list and then do a list sort and sort it alphabetically like so: ListSort(valuelist(get_PH.hier_num, ,), TextNoCase, ASC, ,) or this (will probably run faster) ListSort(valuelist(get_PH.hier_num, ,), Text, ASC, ,) And then loop over the list for your drop down. G On Thu, Apr 3, 2008 at 5:31 PM, Paul Ihrig [EMAIL PROTECTED] wrote: Gerald they are nums in a num field they sort correctly if i wanted them sorted like a number. but powers that be want them sorted in a was to show hierarchy.. nm... i will tell them we can recreat the table with the order they want by hand... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302669 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Order by question
Also, if you're using SQL Server (or MySQL) this one's even easier: SELECT * FROM tmp ORDER BY ascii( c ) ASC -Original Message- From: Andy Matthews [mailto:[EMAIL PROTECTED] Sent: Thursday, April 03, 2008 4:49 PM To: CF-Talk Subject: RE: Order by question Paul... What DBMS are you using? In MySQL 5, sorting a numeric column apparently defaults to sorting alphabetically. CREATE TABLE `tmp` ( `c` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO tmp (c) VALUES (1); INSERT INTO tmp (c) VALUES (101); INSERT INTO tmp (c) VALUES (101010); INSERT INTO tmp (c) VALUES (2); INSERT INTO tmp (c) VALUES (201); INSERT INTO tmp (c) VALUES (201010); INSERT INTO tmp (c) VALUES (201020); SELECT * FROM `tmp` ORDER BY c ASC -Original Message- From: Paul Ihrig [mailto:[EMAIL PROTECTED] Sent: Thursday, April 03, 2008 4:31 PM To: CF-Talk Subject: Re: Order by question Gerald they are nums in a num field they sort correctly if i wanted them sorted like a number. but powers that be want them sorted in a was to show hierarchy.. nm... i will tell them we can recreat the table with the order they want by hand... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302673 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Order by question
its ms sql 2k but alas. it is game/beer time. so i will look at it again in the morning thanks every one for being so helpful On Thu, Apr 3, 2008 at 6:00 PM, Andy Matthews [EMAIL PROTECTED] wrote: Also, if you're using SQL Server (or MySQL) this one's even easier: SELECT * FROM tmp ORDER BY ascii( c ) ASC -Original Message- From: Andy Matthews [mailto:[EMAIL PROTECTED] Sent: Thursday, April 03, 2008 4:49 PM To: CF-Talk Subject: RE: Order by question Paul... What DBMS are you using? In MySQL 5, sorting a numeric column apparently defaults to sorting alphabetically. CREATE TABLE `tmp` ( `c` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO tmp (c) VALUES (1); INSERT INTO tmp (c) VALUES (101); INSERT INTO tmp (c) VALUES (101010); INSERT INTO tmp (c) VALUES (2); INSERT INTO tmp (c) VALUES (201); INSERT INTO tmp (c) VALUES (201010); INSERT INTO tmp (c) VALUES (201020); SELECT * FROM `tmp` ORDER BY c ASC -Original Message- From: Paul Ihrig [mailto:[EMAIL PROTECTED] Sent: Thursday, April 03, 2008 4:31 PM To: CF-Talk Subject: Re: Order by question Gerald they are nums in a num field they sort correctly if i wanted them sorted like a number. but powers that be want them sorted in a was to show hierarchy.. nm... i will tell them we can recreat the table with the order they want by hand... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302674 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Order by question
You can make the field a text data type -or- create another column that is identical and gets updated and everything else just like the other column but make it text and order by that instead ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302686 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: ORDER BY question
Jochem van Dieten wrote: Actually, there is if your database can do a little bit of maths and subqueries in the ORDER BY clause. But it is a bit tricky and get's out of hand when the number of elements in the list get's too long because you need to develop a power series to sort on :) ORDER BY ( 0 cfloop from=1 to=#ListLen(titleIDs)# index=i + (SELECT MAX(TitleID FROM tblJobTitle))^(ListLen(titleIDs) - i) * Round(Abs(tblJobTitle.TitleID - i)/(Abs(tblJobTitle.TitleID - i) + 1)) /cfloop ) DESC I doubt this will be usefull IRL, but it should work once you sort out the typo's you will inevitably get. Conversion to a stored procedure will help as well, you will be able to get the SELECT MAX(TitleID FROM tblJobTitle) out of the loop. Actually, it can be done easier because you are only interested in true/false and not in closeness, which was the original purpose of the code. ORDER BY ( 0 cfloop from=1 to=#ListLen(titleIDs)# index=i + (2^(ListLen(titleIDs) - i) * Round(Abs(tblJobTitle.TitleID - i)/(Abs(tblJobTitle.TitleID - i) + 1)) /cfloop ) DESC Would be interesting to know which one of the proposed solutions is the fastest for your dataset. Jochem __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
Man! you guys went way over my head hear! : ] for this purpose since there are only 4 separate groupings i may just do 4 separate queries. not sure. thought i could do it like Order By tblJobTitle.TitleID=25, tblJobTitle.TitleID=26, tblJobTitle.TitleID=28, tblJobTitle.TitleID=4; -paul __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
ok
tried brians
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]'instr' is not a recognized
function name.
with
cfset newOrderList = ,Partner,Principal,Senior Associate,Associate,
cfquery name=Employees datasource=RPH
cachedwithin=#CreateTimeSpan(0,0,30,0)#
SELECT LEFT(RTrim(tblEmployee.Lastname),10)+', '+tblEmployee.Firstname as
Fullname, tblEmployee.EMPLOYEE_ID, tblEmployee.EmployeeID,
tblEmployee.StudioID, tblEmployee.StatusID, tblEmployee.TitleID,
tblStudio.StudioID, tblStudio.OfficeID, tblStudio.StudioName,
tblOffice.OfficeID, tblOffice.City, tblPhone.PhoneNumb,
tblPhone.PhoneTypeID, tblFloor.floor, tblJobTitle.TitleID, tblJobTitle.Title
FROM tblEmployee, tblStudio, tblOffice, tblPhone, tblFloor, tblJobTitle
WHERE tblEmployee.studioid = tblStudio.studioid
AND tblEmployee.TitleID = tblJobTitle.TitleID
AND tblOffice.OfficeID = tblStudio.OfficeID
AND tblJobTitle.TitleID IN (25,26,28,4)
AND tblPhone.EmpResID = tblEmployee.EmployeeID
AND tblPhone.PhoneTypeID = '1'
!--- AND tblEmployee.StudioID in (#FORM.StudioID#) ---
AND tblEmployee.FloorID = tblFloor.floorid
ORDER BY instr('#newOrderList#',',' | Title | ',')
, Fullname;
/cfquery
-paul
-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 28, 2002 9:55 PM
To: CF-Talk
Subject: RE: ORDER BY question
Ok, I'm an idiot... I just thought of a MUCH faster way to do this
!--- create a list with commas at both ends ---
cfset newOrderList = ,#orderList#,
!--- this query is in ORACLE syntax, but can be done for any DB,
ORDER BY orders by how far in it found the index in your order by
string. ---
cfquery name= ...
SELECT *
FROM myTable
WHERE ID IN (#orderList#) [or whatever here]
ORDER BY instr('#newOrderList#',',' | idColumn | ',')
/cfquery
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
...'If there must be trouble, let it be in my day, that my child may have
peace'...
- Thomas Paine, The American Crisis
-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 28, 2002 6:45 PM
To: CF-Talk
Subject: RE: ORDER BY question
This will do it, but keep a close eye on the processing time of the server,
and make sure if the list gets long that you are using a properly indexed
database.
customTagName.cfm
cfparam name=attributes.sortOrder default=
cfset sqlBase = SELECT * FROM myTable
cfif listLen(attributes.sortOrder) GT 1
cfset sqlString = sqlBase WHERE myColumn =
#replace(attributes.sortOrder,,, UNION ALL #sqlBase# WHERE myColumn =
,all)#
cfelseif attributes.sortOrder NEQ
cfset sqlString = sqlBase WHERE myColumn =
#attributes.sortOrder#
cfelse
cfset sqlString = sqlBase
/cfif
cfquery name= ...
#preserveSingleQuotes(sqlString)#
/cfquery
notice that in the above you are simply creating a SELECT statement for each
item in the specified SORTORDER list and UNIONing them all together.
I chose UNION ALL because it is faster than UNION... UNION filters out
duplicate rows...
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
...'If there must be trouble, let it be in my day, that my child may have
peace'...
- Thomas Paine, The American Crisis
-Original Message-
From: Paul Ihrig [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 28, 2002 11:59 AM
To: CF-Talk
Subject: ORDER BY question
ok. ASC DESC don't work on this one.
i want to be able to ORDER BY in a specific order
such as
ORDER BY tblJobTitle.TitleID='25,26,28,4'
is that possible?
if not how else could i do it...
!--- tblJobTitle.TitleID
Partner=25
Principal=26
Senior Associate=28
Associate=4 ---
Thanks
-paul
__
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]'instr' is not a recognized function name. INSTR is the Access function - CHARINDEX is the SQL Server one The parameters are also the other way around Philip Arnold Technical Director Certified ColdFusion Developer ASP Multimedia Limited Switchboard: +44 (0)20 8680 8099 Fax: +44 (0)20 8686 7911 www.aspmedia.co.uk www.aspevents.net An ISO9001 registered company. ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
nope, didn't do it thanks Jochem -paul -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 28, 2002 8:25 PM To: CF-Talk Subject: Re: ORDER BY question Randell B Adkins wrote: Create a new field called Sequence or something. Based on the data and the two elements you displayed there is no way without Hard-Coding it. Actually, there is if your database can do a little bit of maths and subqueries in the ORDER BY clause. But it is a bit tricky and get's out of hand when the number of elements in the list get's too long because you need to develop a power series to sort on :) ORDER BY ( 0 cfloop from=1 to=#ListLen(titleIDs)# index=i + (SELECT MAX(TitleID FROM tblJobTitle))^(ListLen(titleIDs) - i) * Round(Abs(tblJobTitle.TitleID - i)/(Abs(tblJobTitle.TitleID - i) + 1)) /cfloop ) DESC I doubt this will be usefull IRL, but it should work once you sort out the typo's you will inevitably get. Conversion to a stored procedure will help as well, you will be able to get the SELECT MAX(TitleID FROM tblJobTitle) out of the loop. Jochem __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: ORDER BY question
One of your goals should be to have as few queries as possible. If you want to do it as one query you might do something like: cfquery... select tblJobTitle.Title, 1 as SortCol from tblJobTitle where tblJobTitle.Table_ID = 25 UNION select tblJobTitle.Title, 2 as SortCol from tblJobTitle where tblJobTitle.Table_ID = 26 UNION select tblJobTitle.Title, 3 as SortCol from tblJobTitle where tblJobTitle.Table_ID = 28 UNION select tblJobTitle.Title, 4 as SortCol from tblJobTitle where tblJobTitle.Table_ID = 4 order by 2 /cfquery Here's a rough explanation: Since you're specifying the table ID, you can't have it in your order by. Each query in the UNION select is grabbing the data as if it's a single query. The difference is that you have all 4 results in one recordset. This makes things easier to work with as far as the output. Notice that the order by refers to a number, not a name... when doing a UNION select you have to order by the column number (hence the SortCol field we're creating). Hope this helps! Hatton Paul Ihrig wrote: Man! you guys went way over my head hear! : ] for this purpose since there are only 4 separate groupings i may just do 4 separate queries. not sure. thought i could do it like Order By tblJobTitle.TitleID=25, tblJobTitle.TitleID=26, tblJobTitle.TitleID=28, tblJobTitle.TitleID=4; -paul __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
ok...Philip.
how should the parameters go?
i cant find any info on CHARINDEX
in my books.
cfset newOrderList = ,25,26,28,4,
cfquery name=Employees datasource=RPH
cachedwithin=#CreateTimeSpan(0,0,30,0)#
SELECT LEFT(RTrim(tblEmployee.Lastname),10)+', '+tblEmployee.Firstname as
Fullname, tblEmployee.EMPLOYEE_ID, tblEmployee.EmployeeID,
tblEmployee.StudioID, tblEmployee.StatusID, tblEmployee.TitleID,
tblStudio.StudioID, tblStudio.OfficeID, tblStudio.StudioName,
tblOffice.OfficeID, tblOffice.City, tblPhone.PhoneNumb,
tblPhone.PhoneTypeID, tblFloor.floor, tblJobTitle.TitleID, tblJobTitle.Title
FROM tblEmployee, tblStudio, tblOffice, tblPhone, tblFloor, tblJobTitle
WHERE tblEmployee.studioid = tblStudio.studioid
AND tblEmployee.TitleID = tblJobTitle.TitleID
AND tblOffice.OfficeID = tblStudio.OfficeID
AND tblJobTitle.TitleID IN (25,26,28,4)
AND tblPhone.EmpResID = tblEmployee.EmployeeID
AND tblPhone.PhoneTypeID = '1'
!--- AND tblEmployee.StudioID in (#FORM.StudioID#) ---
AND tblEmployee.FloorID = tblFloor.floorid
ORDER BY CHARINDEX('#newOrderList#',',' | tblJobTitle.TitleID | ',')
, Fullname;
/cfquery
-paul
-Original Message-
From: Philip Arnold - ASP [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 29, 2002 9:04 AM
To: CF-Talk
Subject: RE: ORDER BY question
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]'instr' is not
a recognized function name.
INSTR is the Access function - CHARINDEX is the SQL Server one
The parameters are also the other way around
Philip Arnold
Technical Director
Certified ColdFusion Developer
ASP Multimedia Limited
Switchboard: +44 (0)20 8680 8099
Fax: +44 (0)20 8686 7911
www.aspmedia.co.uk
www.aspevents.net
An ISO9001 registered company.
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**
__
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
CHARINDEX(subString, String, StartPoint)
So, CHARINDEX(',' + tblJobTitle.TitleID + ',' , '#newOrderList#')
As long as NewOrderList begins and ends with commas, this will find the
position of tblJobTitle.TitleID within NewOrderList
Philip Arnold
Technical Director
Certified ColdFusion Developer
ASP Multimedia Limited
Switchboard: +44 (0)20 8680 8099
Fax: +44 (0)20 8686 7911
www.aspmedia.co.uk
www.aspevents.net
An ISO9001 registered company.
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**
__
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
Error Diagnostic Information
ODBC Error Code = 22005 (Error in assignment)
[Microsoft][ODBC SQL Server Driver][SQL Server]The data type int is invalid
for the charindex function.
Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.
cfset newOrderList = ,25,26,28,4,
cfquery name=Employees datasource=RPH
cachedwithin=#CreateTimeSpan(0,0,30,0)#
SELECT
LEFT(RTrim(tblEmployee.Lastname),10)+', '+tblEmployee.Firstname as Fullname,
tblEmployee.EMPLOYEE_ID, tblEmployee.EmployeeID, tblEmployee.StudioID,
tblEmployee.StatusID, tblEmployee.TitleID, tblStudio.StudioID,
tblStudio.OfficeID, tblStudio.StudioName, tblOffice.OfficeID,
tblOffice.City, tblPhone.PhoneNumb, tblPhone.PhoneTypeID, tblFloor.floor,
tblJobTitle.TitleID, tblJobTitle.Title
FROM tblEmployee, tblStudio, tblOffice, tblPhone, tblFloor, tblJobTitle
WHERE tblEmployee.studioid = tblStudio.studioid
AND tblEmployee.TitleID = tblJobTitle.TitleID
AND tblOffice.OfficeID = tblStudio.OfficeID
AND tblJobTitle.TitleID IN (25,26,28,4)
AND tblPhone.EmpResID = tblEmployee.EmployeeID
AND tblPhone.PhoneTypeID = '1'
AND tblEmployee.FloorID = tblFloor.floorid
ORDER BY CHARINDEX(',' + tblJobTitle.TitleID + ',' , '#newOrderList#')
/cfquery
-paul
__
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
this is odd
if i do this
cfset newOrderList = ,'Partner','Principal','Senior
Associate','Associate',
ORDER BY CHARINDEX(',' + Title + ',' , '#newOrderList#'), Fullname;
it basically doesnt even put in the Title as a Order By Item...
so my group by in the outputs dont work..
oh well.
i can fake this one..
look like a good time to use QaQ
-paul
__
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
this is odd if i do this
cfset newOrderList = ,'Partner','Principal','Senior
Associate','Associate',
ORDER BY CHARINDEX(',' + Title + ',' , '#newOrderList#'), Fullname;
it basically doesnt even put in the Title as a Order By Item...
so my group by in the outputs dont work..
oh well.
i can fake this one..
look like a good time to use QaQ
OK, the reason is that your NewOrderList has apostrophies, and I'm
betting that Title doesn't - also, you're not using
PreserveSingleQuotes() on the string in the SQL
Try
ORDER BY CHARINDEX(',''' + Title + ''',' ,
'#PreserveSingleQuotes(newOrderList)#'), Fullname;
Philip Arnold
Technical Director
Certified ColdFusion Developer
ASP Multimedia Limited
Switchboard: +44 (0)20 8680 8099
Fax: +44 (0)20 8686 7911
www.aspmedia.co.uk
www.aspevents.net
An ISO9001 registered company.
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**
__
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
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
Error Diagnostic Information ODBC Error Code = 22005 (Error in assignment) [Microsoft][ODBC SQL Server Driver][SQL Server]The data type int is invalid for the charindex function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary. You can only use Strings in string functions, try; CONVERT(varchar, myInt) Philip Arnold Technical Director Certified ColdFusion Developer ASP Multimedia Limited Switchboard: +44 (0)20 8680 8099 Fax: +44 (0)20 8686 7911 www.aspmedia.co.uk www.aspevents.net An ISO9001 registered company. ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
You are using SQL Server. The function name is not the same in SQL Server
as in Oracle. I can't remember what the exact function is, but it would be
worth your time to look it up...
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
...'If there must be trouble, let it be in my day, that my child may have
peace'...
- Thomas Paine, The American Crisis
-Original Message-
From: Paul Ihrig [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 29, 2002 5:58 AM
To: CF-Talk
Subject: RE: ORDER BY question
ok
tried brians
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]'instr' is not a recognized
function name.
with
cfset newOrderList = ,Partner,Principal,Senior Associate,Associate,
cfquery name=Employees datasource=RPH
cachedwithin=#CreateTimeSpan(0,0,30,0)#
SELECT LEFT(RTrim(tblEmployee.Lastname),10)+', '+tblEmployee.Firstname as
Fullname, tblEmployee.EMPLOYEE_ID, tblEmployee.EmployeeID,
tblEmployee.StudioID, tblEmployee.StatusID, tblEmployee.TitleID,
tblStudio.StudioID, tblStudio.OfficeID, tblStudio.StudioName,
tblOffice.OfficeID, tblOffice.City, tblPhone.PhoneNumb,
tblPhone.PhoneTypeID, tblFloor.floor, tblJobTitle.TitleID, tblJobTitle.Title
FROM tblEmployee, tblStudio, tblOffice, tblPhone, tblFloor, tblJobTitle
WHERE tblEmployee.studioid = tblStudio.studioid
AND tblEmployee.TitleID = tblJobTitle.TitleID
AND tblOffice.OfficeID = tblStudio.OfficeID
AND tblJobTitle.TitleID IN (25,26,28,4)
AND tblPhone.EmpResID = tblEmployee.EmployeeID
AND tblPhone.PhoneTypeID = '1'
!--- AND tblEmployee.StudioID in (#FORM.StudioID#) ---
AND tblEmployee.FloorID = tblFloor.floorid
ORDER BY instr('#newOrderList#',',' | Title | ',')
, Fullname;
/cfquery
-paul
-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 28, 2002 9:55 PM
To: CF-Talk
Subject: RE: ORDER BY question
Ok, I'm an idiot... I just thought of a MUCH faster way to do this
!--- create a list with commas at both ends ---
cfset newOrderList = ,#orderList#,
!--- this query is in ORACLE syntax, but can be done for any DB,
ORDER BY orders by how far in it found the index in your order by
string. ---
cfquery name= ...
SELECT *
FROM myTable
WHERE ID IN (#orderList#) [or whatever here]
ORDER BY instr('#newOrderList#',',' | idColumn | ',')
/cfquery
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
...'If there must be trouble, let it be in my day, that my child may have
peace'...
- Thomas Paine, The American Crisis
-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 28, 2002 6:45 PM
To: CF-Talk
Subject: RE: ORDER BY question
This will do it, but keep a close eye on the processing time of the server,
and make sure if the list gets long that you are using a properly indexed
database.
customTagName.cfm
cfparam name=attributes.sortOrder default=
cfset sqlBase = SELECT * FROM myTable
cfif listLen(attributes.sortOrder) GT 1
cfset sqlString = sqlBase WHERE myColumn =
#replace(attributes.sortOrder,,, UNION ALL #sqlBase# WHERE myColumn =
,all)#
cfelseif attributes.sortOrder NEQ
cfset sqlString = sqlBase WHERE myColumn =
#attributes.sortOrder#
cfelse
cfset sqlString = sqlBase
/cfif
cfquery name= ...
#preserveSingleQuotes(sqlString)#
/cfquery
notice that in the above you are simply creating a SELECT statement for each
item in the specified SORTORDER list and UNIONing them all together.
I chose UNION ALL because it is faster than UNION... UNION filters out
duplicate rows...
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
...'If there must be trouble, let it be in my day, that my child may have
peace'...
- Thomas Paine, The American Crisis
-Original Message-
From: Paul Ihrig [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 28, 2002 11:59 AM
To: CF-Talk
Subject: ORDER BY question
ok. ASC DESC don't work on this one.
i want to be able to ORDER BY in a specific order
such as
ORDER BY tblJobTitle.TitleID='25,26,28,4'
is that possible?
if not how else could i do it...
!--- tblJobTitle.TitleID
Partner=25
Principal=26
Senior Associate=28
Associate=4 ---
Thanks
-paul
RE: ORDER BY question
If you only have 4 groupings then the case statement is the best solution IMHO. It will let you create the sequence column dynamically. Justin -Original Message- From: Paul Ihrig [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 29, 2002 8:40 AM To: CF-Talk Subject: RE: ORDER BY question Man! you guys went way over my head hear! : ] for this purpose since there are only 4 separate groupings i may just do 4 separate queries. not sure. thought i could do it like Order By tblJobTitle.TitleID=25, tblJobTitle.TitleID=26, tblJobTitle.TitleID=28, tblJobTitle.TitleID=4; __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
ok. ASC DESC don't work on this one. i want to be able to ORDER BY in a specific order such as ORDER BY tblJobTitle.TitleID='25,26,28,4' is that possible? if not how else could i do it... !--- tblJobTitle.TitleID Partner=25 Principal=26 Senior Associate=28 Associate=4 --- Thanks -paul __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: ORDER BY question
Create a new field called Sequence or something. Based on the data and the two elements you displayed there is no way without Hard-Coding it. Randy Adkins [EMAIL PROTECTED] 05/28/02 15:39 PM ok. ASC DESC don't work on this one. i want to be able to ORDER BY in a specific order such as ORDER BY tblJobTitle.TitleID='25,26,28,4' is that possible? if not how else could i do it... !--- tblJobTitle.TitleID Partner=25 Principal=26 Senior Associate=28 Associate=4 --- Thanks -paul __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: ORDER BY question
Randell B Adkins wrote: Create a new field called Sequence or something. Based on the data and the two elements you displayed there is no way without Hard-Coding it. Actually, there is if your database can do a little bit of maths and subqueries in the ORDER BY clause. But it is a bit tricky and get's out of hand when the number of elements in the list get's too long because you need to develop a power series to sort on :) ORDER BY ( 0 cfloop from=1 to=#ListLen(titleIDs)# index=i + (SELECT MAX(TitleID FROM tblJobTitle))^(ListLen(titleIDs) - i) * Round(Abs(tblJobTitle.TitleID - i)/(Abs(tblJobTitle.TitleID - i) + 1)) /cfloop ) DESC I doubt this will be usefull IRL, but it should work once you sort out the typo's you will inevitably get. Conversion to a stored procedure will help as well, you will be able to get the SELECT MAX(TitleID FROM tblJobTitle) out of the loop. Jochem __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
The way to do this is definitely by creating a sequence field and ordering by this field. How the sequence is created is up to you... to hard code it you do something like select sequence = case when 'a' then 1 when 'z' then 2 when 'f' then 3 else 4 end), next field from table The case statement will let you evaluate the field and create a sequence based on it, however, looking at your example, I think it would be wiser to add a sequence table that related each job position to a sequence and generate it this way. Justin -Original Message- From: Randell B Adkins [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 28, 2002 3:41 PM To: CF-Talk Subject: Re: ORDER BY question Create a new field called Sequence or something. Based on the data and the two elements you displayed there is no way without Hard-Coding it. Randy Adkins [EMAIL PROTECTED] 05/28/02 15:39 PM ok. ASC DESC don't work on this one. i want to be able to ORDER BY in a specific order such as ORDER BY tblJobTitle.TitleID='25,26,28,4' is that possible? if not how else could i do it... !--- tblJobTitle.TitleID Partner=25 Principal=26 Senior Associate=28 Associate=4 --- Thanks -paul __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
This will do it, but keep a close eye on the processing time of the server, and make sure if the list gets long that you are using a properly indexed database. customTagName.cfm cfparam name=attributes.sortOrder default= cfset sqlBase = SELECT * FROM myTable cfif listLen(attributes.sortOrder) GT 1 cfset sqlString = sqlBase WHERE myColumn = #replace(attributes.sortOrder,,, UNION ALL #sqlBase# WHERE myColumn = ,all)# cfelseif attributes.sortOrder NEQ cfset sqlString = sqlBase WHERE myColumn = #attributes.sortOrder# cfelse cfset sqlString = sqlBase /cfif cfquery name= ... #preserveSingleQuotes(sqlString)# /cfquery notice that in the above you are simply creating a SELECT statement for each item in the specified SORTORDER list and UNIONing them all together. I chose UNION ALL because it is faster than UNION... UNION filters out duplicate rows... +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ ...'If there must be trouble, let it be in my day, that my child may have peace'... - Thomas Paine, The American Crisis -Original Message- From: Paul Ihrig [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 28, 2002 11:59 AM To: CF-Talk Subject: ORDER BY question ok. ASC DESC don't work on this one. i want to be able to ORDER BY in a specific order such as ORDER BY tblJobTitle.TitleID='25,26,28,4' is that possible? if not how else could i do it... !--- tblJobTitle.TitleID Partner=25 Principal=26 Senior Associate=28 Associate=4 --- Thanks -paul __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
Ok, I'm an idiot... I just thought of a MUCH faster way to do this
!--- create a list with commas at both ends ---
cfset newOrderList = ,#orderList#,
!--- this query is in ORACLE syntax, but can be done for any DB,
ORDER BY orders by how far in it found the index in your order by
string. ---
cfquery name= ...
SELECT *
FROM myTable
WHERE ID IN (#orderList#) [or whatever here]
ORDER BY instr('#newOrderList#',',' | idColumn | ',')
/cfquery
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
...'If there must be trouble, let it be in my day, that my child may have
peace'...
- Thomas Paine, The American Crisis
-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 28, 2002 6:45 PM
To: CF-Talk
Subject: RE: ORDER BY question
This will do it, but keep a close eye on the processing time of the server,
and make sure if the list gets long that you are using a properly indexed
database.
customTagName.cfm
cfparam name=attributes.sortOrder default=
cfset sqlBase = SELECT * FROM myTable
cfif listLen(attributes.sortOrder) GT 1
cfset sqlString = sqlBase WHERE myColumn =
#replace(attributes.sortOrder,,, UNION ALL #sqlBase# WHERE myColumn =
,all)#
cfelseif attributes.sortOrder NEQ
cfset sqlString = sqlBase WHERE myColumn =
#attributes.sortOrder#
cfelse
cfset sqlString = sqlBase
/cfif
cfquery name= ...
#preserveSingleQuotes(sqlString)#
/cfquery
notice that in the above you are simply creating a SELECT statement for each
item in the specified SORTORDER list and UNIONing them all together.
I chose UNION ALL because it is faster than UNION... UNION filters out
duplicate rows...
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
...'If there must be trouble, let it be in my day, that my child may have
peace'...
- Thomas Paine, The American Crisis
-Original Message-
From: Paul Ihrig [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 28, 2002 11:59 AM
To: CF-Talk
Subject: ORDER BY question
ok. ASC DESC don't work on this one.
i want to be able to ORDER BY in a specific order
such as
ORDER BY tblJobTitle.TitleID='25,26,28,4'
is that possible?
if not how else could i do it...
!--- tblJobTitle.TitleID
Partner=25
Principal=26
Senior Associate=28
Associate=4 ---
Thanks
-paul
__
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
I just noticed that my reply didn't come through earlier... must have been
when the mail server was acting up...
My thought was that it looks like he's wanting to order by a string version
of the Primary Key rather than a numeric sort. Something like:
1, 10, 11, 12, 13, 14, 15, 2, 22, 3, 343, 4, 40, 42, 555, 70, 6
If that's the case, let the SQL do the work and use a Convert (not tested
for syntax, but I've done this in reverse with SQL Server 7):
SELECT Name, Convert(Key, VarChar(20) as TextKey
From Table
Order By TextKey
.. just a thought ;)
Hatton
-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 28, 2002 9:55 PM
To: CF-Talk
Subject: RE: ORDER BY question
Ok, I'm an idiot... I just thought of a MUCH faster way to do this
!--- create a list with commas at both ends ---
cfset newOrderList = ,#orderList#,
!--- this query is in ORACLE syntax, but can be done for any DB,
ORDER BY orders by how far in it found the index in your order by
string. ---
cfquery name= ...
SELECT *
FROM myTable
WHERE ID IN (#orderList#) [or whatever here]
ORDER BY instr('#newOrderList#',',' | idColumn | ',')
/cfquery
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
...'If there must be trouble, let it be in my day, that my child may have
peace'...
- Thomas Paine, The American Crisis
-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 28, 2002 6:45 PM
To: CF-Talk
Subject: RE: ORDER BY question
This will do it, but keep a close eye on the processing time of the server,
and make sure if the list gets long that you are using a properly indexed
database.
customTagName.cfm
cfparam name=attributes.sortOrder default=
cfset sqlBase = SELECT * FROM myTable
cfif listLen(attributes.sortOrder) GT 1
cfset sqlString = sqlBase WHERE myColumn =
#replace(attributes.sortOrder,,, UNION ALL #sqlBase# WHERE myColumn =
,all)#
cfelseif attributes.sortOrder NEQ
cfset sqlString = sqlBase WHERE myColumn =
#attributes.sortOrder#
cfelse
cfset sqlString = sqlBase
/cfif
cfquery name= ...
#preserveSingleQuotes(sqlString)#
/cfquery
notice that in the above you are simply creating a SELECT statement for each
item in the specified SORTORDER list and UNIONing them all together.
I chose UNION ALL because it is faster than UNION... UNION filters out
duplicate rows...
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
...'If there must be trouble, let it be in my day, that my child may have
peace'...
- Thomas Paine, The American Crisis
-Original Message-
From: Paul Ihrig [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 28, 2002 11:59 AM
To: CF-Talk
Subject: ORDER BY question
ok. ASC DESC don't work on this one.
i want to be able to ORDER BY in a specific order
such as
ORDER BY tblJobTitle.TitleID='25,26,28,4'
is that possible?
if not how else could i do it...
!--- tblJobTitle.TitleID
Partner=25
Principal=26
Senior Associate=28
Associate=4 ---
Thanks
-paul
__
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
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: ORDER BY question
I thought of this, but it won't handle a string with more than one period. Shucks. select Val(Paragraph) as Para from myTable order by Val(Paragraph) Any chance of changing the database so that Paragraph = 7.1.2 is stored as 3 fields: ParaMajor = 7, ParaMinor = 1, ParaReallyMinor = 2? -David On Wed, 10 Jan 2001 06:47:49 -0700 "Erika Foster" [EMAIL PROTECTED] writes: I have a table that has three fields: Paragraph, Title, Content. The Paragraph field is a text field, but is the outline number for the particular paragraph in a document. For example, 7.1.1, 7.1.2, etc. I need to output these paragraphs in outline order as they appear in the printed material. Of course, if the paragraphs go past 9, it gets all muffed up because its a text field and not a number field. Example: 7.1, 7.10, 7.11, 7.2, 7.3... Is there any easy way to accomplish this? Erika Foster engineering-environmental Management Applications Developer (505) 866-1654 [EMAIL PROTECTED] ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
I have a table that has three fields: Paragraph, Title, Content.
The Paragraph field is a text field, but is the outline number for the
particular paragraph in a document. For example, 7.1.1, 7.1.2, etc.
I need to output these paragraphs in outline order as they appear in the
printed material. Of course, if the paragraphs go past 9, it gets all
muffed up because its a text field and not a number field. Example: 7.1,
7.10, 7.11, 7.2, 7.3...
Is there any easy way to accomplish this?
I assume that since you've got 7.? there, it could go up to 10.?
If this is the case, then it's a little more complex, but still workable
Assuming SQL Server;
Order by Int(Val(Paragraph)),
Val(SubString(Paragraph,CharIndex('.',Paragraph)+1,Len(Paragraph)))
What this will do is Int the number of the paragraph (in your example, 7)
and then provide the value of everything after the "." - there is no error
checking for if there isn't a dot (which will produce "7" if it's just "7"),
also it won't handle multiple levels
Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: ORDER BY question
Is it possible to create a paragraph number field? -Original Message- From: Erika Foster [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 10, 2001 11:48 PM To: CF-Talk Subject: ORDER BY question I have a table that has three fields: Paragraph, Title, Content. The Paragraph field is a text field, but is the outline number for the particular paragraph in a document. For example, 7.1.1, 7.1.2, etc. I need to output these paragraphs in outline order as they appear in the printed material. Of course, if the paragraphs go past 9, it gets all muffed up because its a text field and not a number field. Example: 7.1, 7.10, 7.11, 7.2, 7.3... Is there any easy way to accomplish this? Erika Foster engineering-environmental Management Applications Developer (505) 866-1654 [EMAIL PROTECTED] ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Order By Question
U running MS SQL server 7 ? -Original Message- From: Gieseman, Athelene [mailto:[EMAIL PROTECTED]] Sent: 04 December 2000 15:41 To: CF-Talk Subject:OT: Order By Question I've got a query that works fine except that it seems to be ignoring the order by clause. It seems to be sorting in order of the primary key. Any suggestions? The query is: cfquery datasource="HelpDesk" name="EditRecordqry" Select *, DATENAME(MM, CALLDATE) + ' ' + DATENAME(DD, CALLDATE) + ', ' + DATENAME(, CALLDATE)AS 'DATEOFCALL', DATENAME(MM, LASTUPDATE) + ' ' + DATENAME(DD, LASTUPDATE) + ', ' + DATENAME(, LASTUPDATE)AS 'LU', Ext, EmailAddress from CALLS, USERS WHERE (CALLS.Closed='N' or CALLS.Closed IS NULL) AND (CALLS.Name = USERS.Name) order by Users.Name /cfquery I've also tried sorting by CALLS.Name, same result. Athelene Gieseman [EMAIL PROTECTED] ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: order by question
Could we see a the code for your query and/or cfoutput? What you are describing should work fine, so it is probably a little typo or something. -Original Message- From: Jill Cooney [EMAIL PROTECTED] To: '[EMAIL PROTECTED]' [EMAIL PROTECTED] Date: Thursday, June 29, 2000 2:18 PM Subject: SQL: order by question I have a library database which I order by Book Title in order to present an alphabetical list. However, when a new book is added which has the same first word as another title (for example, "Managing Databases" and "Managing Offices"), only "Managing Databases" will appear in the list. If I turn on debugging, I know the second Managing title is being selected, just not "outputted". Does anyone know of a workaround? Thanks. Jill Cooney Webmaster Dataware Technologies http://www.dataware.com --- --- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: order by question - DOH!
In pasting the code into my reply, I realized I'd had a "group=" in my cfoutput -- once I took this out, the list displays fine. Thanks for the push! -- Jill -Original Message- From: Dana Larose [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 29, 2000 12:30 PM To: [EMAIL PROTECTED] Subject: Re: order by question Could we see a the code for your query and/or cfoutput? What you are describing should work fine, so it is probably a little typo or something. -Original Message- From: Jill Cooney [EMAIL PROTECTED] To: '[EMAIL PROTECTED]' [EMAIL PROTECTED] Date: Thursday, June 29, 2000 2:18 PM Subject: SQL: order by question I have a library database which I order by Book Title in order to present an alphabetical list. However, when a new book is added which has the same first word as another title (for example, "Managing Databases" and "Managing Offices"), only "Managing Databases" will appear in the list. If I turn on debugging, I know the second Managing title is being selected, just not "outputted". Does anyone know of a workaround? Thanks. Jill Cooney Webmaster Dataware Technologies http://www.dataware.com --- --- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: order by question
Well, if you've determined that the query runs fine, then the problem is with the output. I would double check your theory by running the query directly in the database. Use iSQL/w on SQL or an Access query, or whatever just to make sure the results are coming out correctly. If the query is in fact good, check your output statements, clear your browser's cache, and try accessing it from another computer. ---mark -- Mark Warrick Phone: (714) 547-5386 Efax.com Fax: (801) 730-7289 Personal Email: [EMAIL PROTECTED] Personal URL: http://www.warrick.net Business Email: [EMAIL PROTECTED] Business URL: http://www.fusioneers.com ICQ: 346566 -- -Original Message- From: Jill Cooney [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 29, 2000 11:14 AM To: '[EMAIL PROTECTED]' Subject: SQL: order by question I have a library database which I order by Book Title in order to present an alphabetical list. However, when a new book is added which has the same first word as another title (for example, "Managing Databases" and "Managing Offices"), only "Managing Databases" will appear in the list. If I turn on debugging, I know the second Managing title is being selected, just not "outputted". Does anyone know of a workaround? Thanks. Jill Cooney Webmaster Dataware Technologies http://www.dataware.com -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf _talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebarRstsbodyRsts/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
