ssage-
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
: 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?=]
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
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_i
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
Date: Wed, June 30, 2010 4:18 pm
To: cf-talk
Works the charm:
SELECT * FROM judges
ORDER BY CASE W
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.
-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
>>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 ap
longer.
>
> ~Brad
>
> Original Message ----
> Subject: Re: Order By question
> From: John M Bliss
> Date: Wed, June 30, 2010 3:16 pm
> To: cf-talk
>
>
> Yeah *that's* why I didn't suggest your more terse ELSE last_name Yeah.
> That&#x
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
Date: Wed, June 30, 2010 3:16 pm
To: cf-talk
Yeah *that's* why I didn't suggest your more terse ELSE last_name Yea
amp; ' ( ) * + , - . /
>
> All of those fall alphabetically before 0.
>
> :)
>
> ~Brad
>
> Original Message
> Subject: RE: Order By question
> From: "Paul Alkema"
> Date: Wed, June 30, 2010 3:06 pm
> To: cf-talk
>
>
> I secont
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"
Date:
n 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
Date: Wed, June 30, 2010
kema.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
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 respect
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 t
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
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 wrote:
>
> Not 100% sure how to do this...
>
> Need to return a list of folks ordered by last_name (no problem), EXCEPT
> for 2
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 wrote:
>
> Not 100% sure
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, versio
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
your code it would be:
SELECT convert(varchar, hier_num) as TextValues, .hier_desc
FROM hierarchy
ORDER BY CONVERT(varchar, hier_num)
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
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
gt; 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...
>
> Wh
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 DB
0
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
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 PROTE
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
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:
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 shoul
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 li
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...
~
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...
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 sort
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 l
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
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 orde
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
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...
SELECT hier_n
age-
> From: Les Mizzell [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, December 19, 2007 12:44 PM
> To: CF-Talk
> Subject: cfquery "order by" question
>
> i have an adin form that allows a client to specify the sort
> order of a returned collection of results. E
Coalesce kicks hiney. It's so useful.
-Original Message-
From: Les Mizzell [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 19, 2007 12:15 PM
To: CF-Talk
Subject: Re: cfquery "order by" question
> Use something like
> ORDER BY COALESCE(sortORD,) ASC, #g
Coalesce simply returns the first non-null value. Like MS SQL's isnull,
but it can take more arguments.
~Brad
-Original Message-
From: Les Mizzell [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 19, 2007 12:15 PM
To: CF-Talk
Subject: Re: cfquery "order by" question
> Use something like
> ORDER BY COALESCE(sortORD,) ASC, #getPAGE.sortMETH#
Ooo - that works.
Actually, I'll admit to having never seen "coalesce" before.
Thanks very much! That eliminates something far more convoluted!
Les
~~~
> I *think* I understand what you are asking.
> What is the sortORD column value for the other 12 records you want to
> come after the first three?
Currently null, though I could set it to default to something else.
I suppose "" might be a good idea actually, then there would be
a valu
> How do I get my numberic sort fields to take priority and come up first
> without maybe having TWO queries defined and two output blocks?
Use something like
ORDER BY COALESCE(sortORD,) ASC, #getPAGE.sortMETH#
~|
Adobe® Cold
like above should work.
~Brad
-Original Message-
From: Les Mizzell [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 19, 2007 11:44 AM
To: CF-Talk
Subject: cfquery "order by" question
i have an adin form that allows a client to specify the sort order of a
returned collec
i have an adin form that allows a client to specify the sort order of a
returned collection of results. Easy enough, but I've run into a slight
snag...
Here's the basic query...
Select name, amount, date
FROM COLLECTIONS
WHERE collection_id = '#url.colID#'
ORDER BY sortORD ASC
ject: 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.TitleI
riginal 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]&#
> 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
> this is odd if i do this
>
>
> 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 r
this is odd
if i do this
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
___
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.
SELECT
LEFT(RTrim(tblEmployee.Lastname),10)
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
INDEX('#newOrderList#',',' | tblJobTitle.TitleID | ',')
, Fullname;
-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 I
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:
select tblJobTitle.Title, 1 as SortCol
from tblJobTitle
where tblJobTitle.Table_ID = 25
UNION
select tblJobTitle.Title, 2 as SortCol
from tblJobTitle
where tblJobTitle
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
> 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
P
lEmployee.EmployeeID
AND tblPhone.PhoneTypeID = '1'
AND tblEmployee.FloorID = tblFloor.floorid
ORDER BY instr('#newOrderList#',',' | Title | ',')
, Fullname;
-paul
-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May
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
___
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 :
ginal 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
SELECT *
FROM myTable
WHERE ID IN (#o
y 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,
a
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 wan
> 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 Ha
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 tric
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 a
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...
Thanks
-paul
__
Signup for the Fu
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...
Thanks
-paul
__
Get the mailserv
, 2001 10:57 AM
To: CF-Talk
Subject: 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
> 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
> printe
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?
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
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.
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
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:
Select *, DATENAME(MM, CALLDATE) + ' ' + DATENAME(DD, CALLDATE) + ', ' +
DATENAME(, CALLDATE)AS 'DATEOFCALL',
DATEN
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 anoth
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]' <
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
>&
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
80 matches
Mail list logo
