Home » SQL & PL/SQL » SQL & PL/SQL » Sorting Problem while generating table dynamically ? (Oracle 10 g)
Sorting Problem while generating table dynamically ? [message #392255] Tue, 17 March 2009 03:25 Go to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
Hi,

I am having table test_15 which is populated dynamically.i want to sort that table.

pls help


create table test_15( no int, name varchar(15),age int,dept varchar(10));

insert into test_15 values (1,'AAA',15,'A');
insert into test_15 values (2,'BAA',16,'A');
insert into test_15 values (3,'CAA',18,'A');
insert into test_15 values (4,'DAA',19,'A');
insert into test_15 values (5,'EAA',24,'B');
insert into test_15 values (6,'FAA',23,'B');
insert into test_15 values (7,'GAA',26,'C');
insert into test_15 values (8,'HAA',27,'C');

SQL> select * from test_15;

                                     NO NAME                                                AGE DEPT
--------------------------------------- --------------- --------------------------------------- ----------
                                      1 AAA                                                  15 A
                                      2 BAA                                                  16 A
                                      3 CAA                                                  18 A
                                      4 DAA                                                  19 A
                                      5 EAA                                                  23 B
                                      6 FAA                                                  24 B
                                      7 GAA                                                  25 C
                                      8 HAA                                                  26 C

i want to change the order for dept B alone in descending order
how to do it

i am populating this table dynamically .


Thanks
Rangan S

[Updated on: Tue, 17 March 2009 03:49] by Moderator

Report message to a moderator

Re: Sorting Problem while generating table dynamically ? [message #392259 is a reply to message #392255] Tue, 17 March 2009 03:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you want your output sorted, you should always use ORDER BY.
Inserting order does not guarantee anything about the order in the result set.
Re: Sorting Problem while generating table dynamically ? [message #392266 is a reply to message #392255] Tue, 17 March 2009 04:02 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@rangan.s,

No contradictions there with Frank's reply but can you clarify what you meant by the following?

rangan.s wrote on Tue, 17 March 2009 13:55

i want to change the order for dept B alone in descending order
how to do it



So does that mean Dept B should be in descending order and all the other departments(Dept A and C) should be in ascending order?Also, based on what column are you ordering?

[Edit: typo]

Regards,
Jo

[Updated on: Tue, 17 March 2009 04:03]

Report message to a moderator

Re: Sorting Problem while generating table dynamically ? [message #392267 is a reply to message #392259] Tue, 17 March 2009 04:21 Go to previous messageGo to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
I am expecting the result should be


SQL> select * from test_15;

                                     NO NAME                                                AGE DEPT
--------------------------------------- --------------- --------------------------------------- ----------
                                      1 AAA                                                  15 A
                                      2 BAA                                                  16 A
                                      3 CAA                                                  18 A
                                      4 DAA                                                  19 A
                                      6 EAA                                                  24 B
                                      5 FAA                                                  23 B
                                      7 GAA                                                  26 C
                                      8 HAA                                                  27 C

Re: Sorting Problem while generating table dynamically ? [message #392269 is a reply to message #392267] Tue, 17 March 2009 04:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use DECODE or CASE in the ORDER BY clause to distinguish between B and not B
Re: Sorting Problem while generating table dynamically ? [message #392319 is a reply to message #392255] Tue, 17 March 2009 07:05 Go to previous messageGo to next message
traj_oracle
Messages: 1
Registered: February 2009
Location: Chennai
Junior Member
Hi,

you should sort the column use to ORDER BY [Asc,Desc] . Suppose you did not give anything to order by . It's take default to ascending order.
Re: Sorting Problem while generating table dynamically ? [message #392323 is a reply to message #392319] Tue, 17 March 2009 07:18 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@traj_oracle,

Welcome to the Forum. Please follow up the post till the very end and then reply. Did you go through Frank's reply:
Quote:

Use DECODE or CASE in the ORDER BY clause to distinguish between B and not B


Thanks for contributing anyways.

Regards,
Jo
Re: Sorting Problem while generating table dynamically ? [message #392329 is a reply to message #392319] Tue, 17 March 2009 07:59 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
traj_oracle wrote on Tue, 17 March 2009 08:05

Suppose you did not give anything to order by . It's take default to ascending order.



This is a ridiculous statement in more ways than one.
Re: Sorting Problem while generating table dynamically ? [message #392368 is a reply to message #392329] Tue, 17 March 2009 12:12 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Hmmm... And here I was thinking he has mentioned, If the ASC or DESC value is omitted in ORDER BY column_name(s) [ASC/DESC], it is sorted by ASC.
Sorry!!! My bad... ./fa/3518/0/
Re: Sorting Problem while generating table dynamically ? [message #392387 is a reply to message #392368] Tue, 17 March 2009 14:02 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Maybe you are right. I can't tell from that message. One of those "ridiculous" items I meant was the sentence is not grammatically correct, so who knows what it is supposed to mean.
Previous Topic: Error while compliling the code
Next Topic: SQL Query
Goto Forum:
  


Current Time: Fri Dec 02 18:30:49 CST 2016

Total time taken to generate the page: 0.21363 seconds