Query regarding order by?? [message #388007] |
Mon, 23 February 2009 00:27  |
aviva4500
Messages: 122 Registered: July 2008 Location: bangalore
|
Senior Member |
|
|
Dear All,
I have a table named test1 which has a column named id and the data type is varchar2(20).And i have one more table which is named as test2 and the data type is integer.
SQL> DESC TEST1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID VARCHAR2(20)
SQL> SELECT * FROM TEST1;
ID
--------------------
1
3
101
102
103
10
6 rows selected.
SQL> SELECT * FROM TEST1
2 ORDER BY ID;
ID
--------------------
1
10
101
102
103
3
6 rows selected.
SQL> SELECT * FROM TEST1
2 ORDER BY ID ASC;
ID
--------------------
1
10
101
102
103
3
6 rows selected.
SQL> SELECT * FROM TEST1
2 ORDER BY ID DESC;
ID
--------------------
3
103
102
101
10
1
6 rows selected.
SQL> CREATE TABLE TEST2(ID INTEGER);
Table created.
SQL> INSERT INTO TEST2 VALUES(1);
1 row created.
SQL> INSERT INTO TEST2 VALUES(3);
1 row created.
SQL> INSERT INTO TEST2 VALUES(101);
1 row created.
SQL> INSERT INTO TEST2 VALUES(102);
1 row created.
SQL> INSERT INTO TEST2 VALUES(103);
1 row created.
SQL> INSERT INTO TEST2 VALUES(10);
1 row created.
SQL> DESC TEST2;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
SQL> SELECT * FROM TEST2;
ID
----------
1
3
101
102
103
10
6 rows selected.
SQL> SELECT * FROM TEST2
2 ORDER BY ID;
ID
----------
1
3
10
101
102
103
6 rows selected.
SQL>
Here in the above example table named test1 is not sorted.But test2 is sorted.So, here my doubt is the column named id with varchar2(20) can be sorted or it cannot be.I know that the data type is varchar2() but even though it accepts the integer values.What is the reson behind this that it is not sorting.
Sorry if this is a simple question.
Regards,
Hammer
[Updated on: Mon, 23 February 2009 00:34] Report message to a moderator
|
|
|
Re: Query regarding order by?? [message #388011 is a reply to message #388007] |
Mon, 23 February 2009 00:34   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Obviously, it can be sorted.
The fact is: numbers are different from characters. "test1" table contains characters (so ORDER BY sorted values as characters (where '100' is placed in front of '2', for example)), while "test2" contains numbers which are sorted as, well, numbers.
[Updated on: Mon, 23 February 2009 00:35] Report message to a moderator
|
|
|
|
Re: Query regarding order by?? [message #388017 is a reply to message #388011] |
Mon, 23 February 2009 00:39   |
aviva4500
Messages: 122 Registered: July 2008 Location: bangalore
|
Senior Member |
|
|
Hi LittleFoot,
Thanks for your immediate reply.So the column named id which has the datatype should be entered sequencially or i can be in random so that we can sort later.
Regards,
Hammer
|
|
|
|
|
|
|
|
|
Re: Query regarding order by?? [message #388067 is a reply to message #388063] |
Mon, 23 February 2009 01:47   |
 |
MarcS
Messages: 312 Registered: March 2007 Location: Antwerp
|
Senior Member |
|
|
Littlefoot wrote on Mon, 23 February 2009 08:41 | OK, the answer to MarcS's question is an easy one.
Though, the question is: what will you do once someone enters, for example, 'A300' into that column? That will be perfectly fine as column's datatype is CHARACTER. However, it will make it impossible to use a function MarcS is talking about as it will cause the INVALID NUMBER error.
So, if you want to store numbers only, then use NUMBER datatype.
|
True enough, but that's not within the scope of the problem now is it 
I'm just answering to the question posted here, not adding caveats.
I can imagine that the next problem could very well be the INVALID NUMBER problem.
But that's an entirely different problem than the one posted.
|
|
|
|
|
Re: Query regarding order by?? [message #388072 is a reply to message #388070] |
Mon, 23 February 2009 02:08   |
aviva4500
Messages: 122 Registered: July 2008 Location: bangalore
|
Senior Member |
|
|
Hi MarcS,
Yes,i got something new regarding this topic.So,in a table if a column has a datatype in varchar2(20),but it only has numbers,but in future it will not be entered as 'A300'.It will have only numbers in that specified column.The only solution is to change the datatype of tha particular column to integer and to sort the results?
Regards,
Hammer.
|
|
|
|
Re: Query regarding order by?? [message #388077 is a reply to message #388072] |
Mon, 23 February 2009 02:17   |
 |
MarcS
Messages: 312 Registered: March 2007 Location: Antwerp
|
Senior Member |
|
|
aviva4500 wrote on Mon, 23 February 2009 09:08 | Hi MarcS,
Yes,i got something new regarding this topic.So,in a table if a column has a datatype in varchar2(20),but it only has numbers,but in future it will not be entered as 'A300'.It will have only numbers in that specified column.The only solution is to change the datatype of tha particular column to integer and to sort the results?
Regards,
Hammer.
|
There is no such thing as "the only solution"
Only the "best" solution that fits your needs.
If you're dealing with numbers, before, now and in the future: store it as a number!
Now on to "change the datatype".
Oracle won't allow the datatype of a column being changed when it contains data ( read: the table has records in it and the column you're about to change is not empty ).
If it's a new table: no problem, create the table with the column having datatype NUBMER.
If the table exist and has data in it: a number of possible solutions are at hand, but the most appropriate one depends on your specific situation
|
|
|
Re: Query regarding order by?? [message #388095 is a reply to message #388077] |
Mon, 23 February 2009 03:27   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I think the OP has missed the point that the data can be sorted numerically in a Varchar2 column, by using ORDER BY to_number(column_name)
Admittedly, it is a bad idea to do that if there is a possibility of there being non-numeric in the column, but that's really their decision to make.
|
|
|
|