Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
Order by question
Author: Jenny Gavin-Wear
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#303222
Oh, and my solution is for MS SQL 2000 ...
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
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.
Author: Jenny Gavin-Wear
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#303221
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
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.
Author: Bobby Hartsfield
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#302686
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
Author: Paul Ihrig
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#302674
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 <lists@commadelimited.com>
wrote:
----- Excess quoted text cut - see Original Post for more -----
Author: Andy Matthews
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#302673
Also, if you're using SQL Server (or MySQL) this one's even easier:
SELECT *
FROM tmp
ORDER BY ascii( c ) ASC
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
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...
Author: Andy Matthews
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#302672
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
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
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...
Author: Greg Morphis
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#302671
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 <lists@commadelimited.com>
wrote:
----- Excess quoted text cut - see Original Post for more -----
Author: Andy Matthews
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#302670
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
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...
Author: Ian Skinner
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#302668
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.
Author: Gerald Guido
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#302669
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 <gerald.guido@gmail.com>
wrote:
----- Excess quoted text cut - see Original Post for more -----
Author: Gerald Guido
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#302666
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 <pihrig@gmail.com> wrote:
----- Excess quoted text cut - see Original Post for more -----
Author: Paul Ihrig
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#302665
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...
Author: Paul Ihrig
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#302664
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...
Author: Gerald Guido
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#302663
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 <pihrig@gmail.com> wrote:
----- Excess quoted text cut - see Original Post for more -----
Author: Paul Ihrig
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#302662
it is not sorting this way...
i Want it to sort this way
On Thu, Apr 3, 2008 at 4:55 PM, Ian Skinner <HOF@ilsweb.com> wrote:
----- Excess quoted text cut - see Original Post for more -----
Author: Kris Jones
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#302659
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 <pihrig@gmail.com> wrote:
----- Excess quoted text cut - see Original Post for more -----
Author: morgan l
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#302660
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 <HOF@ilsweb.com> wrote:
----- Excess quoted text cut - see Original Post for more -----
Author: Ian Skinner
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#302658
Paul Ihrig wrote:
----- Excess quoted text cut - see Original Post for more -----
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.
Author: Paul Ihrig
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55879#302657
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.
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
