Home » SQL & PL/SQL » SQL & PL/SQL » Query (merged 3)
Query (merged 3) [message #365281] Thu, 11 December 2008 05:18 Go to next message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member
Hi All,

I have a table ABC with following sturcture.

Desc abc

id number
section varchar2(10)

select * from abc;

ID SECTION
------------------------
1 REC
2 CP
3 TC
4 TC
5 CP
6 REC
7 CP
8 CP

I want to sort them in such a manner that every section come only after repetition of other available sections, e.g.

ID SECTION
------------------------
2 CP
1 REC
3 TC
5 CP
6 REC
4 TC
7 CP
8 CP


Thanks in advance.
Re: Query [message #365285 is a reply to message #365281] Thu, 11 December 2008 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Query [message #365286 is a reply to message #365281] Thu, 11 December 2008 05:26 Go to previous messageGo to next message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member
No Message Body

[Updated on: Thu, 11 December 2008 05:52]

Report message to a moderator

Re: Query [message #365288 is a reply to message #365281] Thu, 11 December 2008 05:36 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
Use partition by

Analytical function - over Partition will help

[Updated on: Thu, 11 December 2008 05:36]

Report message to a moderator

Re: Query [message #365293 is a reply to message #365285] Thu, 11 December 2008 05:43 Go to previous messageGo to next message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member
Sorry Michel,

I will keep in mind.

This is test data.

create table abc
(id number,
SECTION varchar2(10)
);

insert into abc
values (1,'REC');
insert into abc
values (2,'CP');
insert into abc
values (3,'TC');
insert into abc
values (4,'TC');
insert into abc
values (5,'CP');
insert into abc
values (6,'REC');
insert into abc
values (7,'CP');
insert into abc
values (8,'CP');

commit;

Select * from ABC;

ID SECTION
------------------------
1 REC
2 CP
3 TC
4 TC
5 CP
6 REC
7 CP
8 CP


Now I want to sort them in such a manner that every section come
only after repetition of other available sections, e.g.

ID SECTION
------------------------
2 CP
1 REC
3 TC
5 CP
6 REC
4 TC
7 CP
8 CP


Regards,
Akash


Re: Query [message #365295 is a reply to message #365293] Thu, 11 December 2008 05:49 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
O.k I assume you want to order by id and also the preference should be given to CP, REC and TC.

Clue is already been given to in one of your duplicate post. I repeat the same clue again. User analytic function for example row_number() with partition clause and order by <column> on which you want to do the ordering. Use this order your final result along with the customised ordering on the text.

Regards

Raj
Re: Query [message #365298 is a reply to message #365293] Thu, 11 December 2008 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I will keep in mind.

Not just in mind, do it. I bet you didn't use Preview not code tags.

Have a look at ROW_NUMBER function.

Regards
Michel
Re: Query [message #365301 is a reply to message #365295] Thu, 11 December 2008 05:57 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now I merged the topics.
STOP multiposting and follow Forum Guide.

Regards
Michel
Previous Topic: Comma Separated Number
Next Topic: DBMS_STATS package
Goto Forum:
  


Current Time: Tue Dec 06 14:01:51 CST 2016

Total time taken to generate the page: 0.12073 seconds