Solved

Order By Clause (goes from 1 to 10

Posted on 2012-04-03
11
227 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
 
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
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 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.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now