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.

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.

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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.

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.