Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Order By Clause (goes from 1 to 10

Posted on 2012-04-03
11
Medium Priority
?
243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 77

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 77

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
Industry Leaders: 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!

 
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 77

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 77

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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

609 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