Solved

Order By Clause (goes from 1 to 10

Posted on 2012-04-03
11
234 Views
Last Modified: 2012-04-03
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
Comment
Question by:gnivkor
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37801651
>>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
 

Author Comment

by:gnivkor
ID: 37801669
not sure that I am understanding
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37801707
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 37

Expert Comment

by:Neil Russell
ID: 37801720
"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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37801732
>>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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37801758
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
 
LVL 37

Expert Comment

by:Neil Russell
ID: 37801769
@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
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37801808
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
 

Author Comment

by:gnivkor
ID: 37801822
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37801832
>>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
 

Author Closing Comment

by:gnivkor
ID: 37801922
thank you, worked
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question