• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

Order By Clause (goes from 1 to 10

the sql statement before works without a problem except that when i order instead of it ordering a field 1,2,3,4,5, etc it will order 1,11,12,13, etc before it goes to 2, can someone please help. thanks  

SELECT raw_data.receiver_warehouse,
    store_seq.wag_num,
    store_seq.cust_num,
    store_seq.Customer,
    store_seq.Address,
    store_seq.City,
    store_seq.State,
    raw_data.Vendor,
    raw_data.CustItemCode,
    raw_data.UoM,
    raw_data.Ord,
    raw_data.ship_date,
    raw_data.tkg_num,
    store_seq.Stop,
    store_seq.Route,
    Milk_Items.Item_num
  FROM (raw_data
  INNER JOIN store_seq
  ON raw_data.receiver_warehouse = store_seq.CHR_Description)
  LEFT JOIN Milk_Items
  ON raw_data.CustItemCode = Milk_Items.CustItemCode
WHERE "STORE_SEQ"."CHR_DESCRIPTION" = "RAW_DATA"."RECEIVER_WAREHOUSE"
ORDER BY store_seq.route, store_seq.stop;
0
gnivkor
Asked:
gnivkor
  • 5
  • 3
  • 2
  • +1
1 Solution
 
slightwv (䄆 Netminder) Commented:
>>order 1,11,12,13, etc

ascii/text sort not a numeric sort.

Use to_number on the column to force a numeric sort.  Note this will error if there is non-numeric data.
0
 
gnivkorAuthor Commented:
not sure that I am understanding
0
 
slightwv (䄆 Netminder) Commented:
You sort order you said wasn't correct was sorting on ascii values.
11 is before 2 in ascii values.

You need to make it numeric.

run the example blow to demonstrate what I am saying.
drop table tab1 purge;
create table tab1(col1 varchar2(10));

insert into tab1 values('1');
insert into tab1 values('11');
insert into tab1 values('2');
commit;
select col1 from tab1 order by col1;
select col1 from tab1 order by to_number(col1);

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Neil RussellTechnical Development LeadCommented:
"ORDER BY store_seq.route, store_seq.stop;"

IF your fields are NOT defined as a NUMERIC datatype in the table definition then they will be ascii. An ascii sort will ALWAYS consider 1,11,111 as coming BEFORE 2.

So you need to convert the string to a real number when you do the SELECT
0
 
slightwv (䄆 Netminder) Commented:
>>So you need to convert the string to a real number when you do the SELECT

Not the select but the order by.  Also, I believe this was already posted.
0
 
slightwv (䄆 Netminder) Commented:
To further explain, When you order by a string it usees ascii values to perform the sort.

Check out the ascii values:
http://www.asciitable.com/

The character '1' has a decimal ascii value is 49.  The character '2' is 50.

Since 49 is less than 50, all strings starting with a '1' will come before any string starting with a '2'.
0
 
Neil RussellTechnical Development LeadCommented:
@slightwv
Yes it was posted WHILE I was typing. Posts within the same minute hardly count as copying!!

Anyway, have a read here about converting from type to type if you are unsure.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm
0
 
wdosanjosCommented:
Here is the rewritten SQL with the required conversion to fix the sorting issue as already explained by @slightwv above:

SELECT raw_data.receiver_warehouse,
    store_seq.wag_num,
    store_seq.cust_num,
    store_seq.Customer,
    store_seq.Address,
    store_seq.City,
    store_seq.State,
    raw_data.Vendor,
    raw_data.CustItemCode,
    raw_data.UoM,
    raw_data.Ord,
    raw_data.ship_date,
    raw_data.tkg_num,
    store_seq.Stop,
    store_seq.Route,
    Milk_Items.Item_num
  FROM (raw_data
  INNER JOIN store_seq
  ON raw_data.receiver_warehouse = store_seq.CHR_Description)
  LEFT JOIN Milk_Items
  ON raw_data.CustItemCode = Milk_Items.CustItemCode
WHERE "STORE_SEQ"."CHR_DESCRIPTION" = "RAW_DATA"."RECEIVER_WAREHOUSE"
ORDER BY TO_NUMBER(store_seq.route), TO_NUMBER(store_seq.stop);

Open in new window

0
 
gnivkorAuthor Commented:
SELECT raw_data.receiver_warehouse,
    store_seq.wag_num,
    store_seq.cust_num,
    store_seq.Customer,
    store_seq.Address,
    store_seq.City,
    store_seq.State,
    raw_data.Vendor,
    raw_data.CustItemCode,
    raw_data.UoM,
    raw_data.Ord,
    raw_data.ship_date,
    raw_data.tkg_num,
    store_seq.Stop,
    store_seq.Route,
    Milk_Items.Item_num
  FROM (raw_data
  INNER JOIN store_seq
  ON raw_data.receiver_warehouse = store_seq.CHR_Description)
  LEFT JOIN Milk_Items
  ON raw_data.CustItemCode = Milk_Items.CustItemCode
WHERE "STORE_SEQ"."CHR_DESCRIPTION" = "RAW_DATA"."RECEIVER_WAREHOUSE"
ORDER BY to_number(store_seq.stop), to_number(store_seq.route)


problem is now its only sorts "stop" correctly but it does not sort route

stop      route
1      656
1      888
2      656
2      888
3      656
3      888
4      656
4      888

what i need is
1      656
2      656
3      656
1      888
2      888
3      888
0
 
slightwv (䄆 Netminder) Commented:
>>what i need is

then you don't need to order by STOP first:
ORDER BY to_number(store_seq.route), to_number(store_seq.stop)
0
 
gnivkorAuthor Commented:
thank you, worked
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now