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

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

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

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:

RE: Order By question

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

RE: Order By question

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

RE: Order By question

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

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

Re: Order By question

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

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

Re: Order By question

. ~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

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

RE: Order By question

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

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.

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

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

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

RE: Order By question

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

RE: Order By question

- 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

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

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

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

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

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

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

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

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...

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...

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

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

RE: Order by question

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

Re: Order by question

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

RE: Order by question

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

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:

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

Re: Order by question

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

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

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 :)

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

RE: ORDER BY question

('#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

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

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

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

RE: ORDER BY question

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

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

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

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

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

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

RE: ORDER BY question

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

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

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

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

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

RE: ORDER BY question

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

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 *

RE: ORDER BY question

: 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

RE: ORDER BY question

: 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

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?

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

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

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

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:

RE: order by question - DOH!

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 PROTE

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