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
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
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
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:
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
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
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
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
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
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
.
~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
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
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
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.
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
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
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
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
-
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
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
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
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
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
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
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
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
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...
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...
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
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
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
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
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
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:
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
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
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
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 :)
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
('#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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 *
: 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
: 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
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?
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
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
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
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:
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
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
65 matches
Mail list logo