Home » SQL & PL/SQL » SQL & PL/SQL » Query regarding order by?? (Oracle 10g)
Query regarding order by?? [message #388007] Mon, 23 February 2009 00:27 Go to next message
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 Go to previous messageGo to next message
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 #388015 is a reply to message #388007] Mon, 23 February 2009 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
A table is a set/heap and a set/heap has no order. Full stop.
But if you talk about "order by" queries then both are sorted regarding their datatype.

Regards
Michel

[Updated on: Mon, 23 February 2009 00:39]

Report message to a moderator

Re: Query regarding order by?? [message #388017 is a reply to message #388011] Mon, 23 February 2009 00:39 Go to previous messageGo to next message
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 #388024 is a reply to message #388017] Mon, 23 February 2009 00:48 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel has already answered that; if you want to return records in a specific order, you'll have to (read: you MUST) use an ORDER BY clause. The way you enter records is irrelevant; though, sometimes Oracle may produce result which will "fool you" as it will return records in some kind of an order. Never rely on that, always, but ALWAYS use ORDER BY.

There've been quite a few discussions about the issue on the Forum, AskTom, ... everywhere.
Re: Query regarding order by?? [message #388040 is a reply to message #388024] Mon, 23 February 2009 01:06 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Thanks Michel and Little Foot,
But a small clarification why it was not sorted eventhough i used order by clause,in the table1.

Thanks and Regards,
Hammer
Re: Query regarding order by?? [message #388042 is a reply to message #388040] Mon, 23 February 2009 01:09 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
aviva4500 wrote on Mon, 23 February 2009 08:06
Thanks Michel and Little Foot,
But a small clarification why it was not sorted eventhough i used order by clause,in the table1.

Thanks and Regards,
Hammer


It was sorted, as explained in a previous post, but ALPHABETICALLY. Column id is of type VARCHAR(20) so everything in that column is considered as strings and sorted accordingly.

Same behavior you will have in excel.

Re: Query regarding order by?? [message #388056 is a reply to message #388042] Mon, 23 February 2009 01:29 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear MarcS,
Thank you for your reply but what needs to be done if we need to get the result like the below..
ID
----------
1
3
10
101
102
103


Regards,
Hammer

[Updated on: Mon, 23 February 2009 01:30]

Report message to a moderator

Re: Query regarding order by?? [message #388058 is a reply to message #388007] Mon, 23 February 2009 01:32 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Think a bit, and you'll come up with an answer and solution Wink

It's a common practice in programming: you have String, but you need to work with as Numbers. So what do you do then?
Re: Query regarding order by?? [message #388063 is a reply to message #388058] Mon, 23 February 2009 01:41 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
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.
Re: Query regarding order by?? [message #388067 is a reply to message #388063] Mon, 23 February 2009 01:47 Go to previous messageGo to next message
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 Wink

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 #388068 is a reply to message #388067] Mon, 23 February 2009 01:52 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm afraid that you got me wrong, Marc. I didn't complain about your answer, I'm just saying that - if sorting numeric values stored into a character datatype column causes problems - one should consider storing them into a number datatype column (which is natural) /forum/fa/3314/0/.
Re: Query regarding order by?? [message #388070 is a reply to message #388068] Mon, 23 February 2009 02:00 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Littlefoot wrote on Mon, 23 February 2009 08:52
I'm afraid that you got me wrong, Marc. I didn't complain about your answer, I'm just saying that - if sorting numeric values stored into a character datatype column causes problems - one should consider storing them into a number datatype column (which is natural) /forum/fa/3314/0/.


I totally agree: if you would like values order numerically, than by all means store them as numbers!

But, I got the impression that aviva4500 wasn't aware of the fact that ordering Strings is done differently than ordering Numbers.

If he/she understands this, then we can move on to the next step: store values in the appropriate format. But that's a design issue.


Re: Query regarding order by?? [message #388072 is a reply to message #388070] Mon, 23 February 2009 02:08 Go to previous messageGo to next message
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 #388075 is a reply to message #388072] Mon, 23 February 2009 02:13 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, that might be a good idea.
Re: Query regarding order by?? [message #388077 is a reply to message #388072] Mon, 23 February 2009 02:17 Go to previous messageGo to next message
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" Wink
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 Go to previous messageGo to next message
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.
Re: Query regarding order by?? [message #388098 is a reply to message #388095] Mon, 23 February 2009 03:31 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, yes - that's what we have discussed the whole morning.
Previous Topic: Invalid zipped file after using UTL_FILE and UTL_SMTP
Next Topic: Rearrange column values
Goto Forum:
  


Current Time: Thu Feb 06 10:39:15 CST 2025