Home » SQL & PL/SQL » SQL & PL/SQL » query for output in group
query for output in group [message #197412] Wed, 11 October 2006 03:30 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
can any one point out any function that Oracle provides for grouping for analysis..

I have table with columns
id    tag_col
1     20061025AUD0001-1
2     20061025USD0005-1
3     20061025USD0001-2
4     20061025GBP0007-4
5     20061025AUD0001-3
6     20061025GBP0007-3
7     20061025AUD0001-4
8     20061025GBP0007-2
9     20061025USD0005-3
10    20061025AUD0001-2
11    20061025GBP0007-1
12    20061025USD0005-4
13    20061025AUD0000-7
14    20061025USD0000-7
15    20061025GBP0000-7


and want to output as
13    20061025AUD0000-7
1     20061025AUD0001-1
10    20061025AUD0001-2

5     20061025AUD0001-3
7     20061025AUD0001-4

15    20061025GBP0000-7
11    20061025GBP0007-1
8     20061025GBP0007-2

6     20061025GBP0007-3
4     20061025GBP0007-4

14    20061025USD0000-7
2     20061025USD0005-1
3     20061025USD0005-2

9     20061025USD0005-3
12    20061025USD0005-4


i am doing a order by first by substr(tag_col,1,11),rpad(substr(tag_col,12,4)),to_number(substr(tag_col,17,1))

but could not get into a desired result
also is there any way to always group of 7,1,2 and 3,4 of substr(tag_col9,3)
Re: query for output in group [message #197434 is a reply to message #197412] Wed, 11 October 2006 04:41 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Taking your somewhat bizarre requirements at face level, here is a SQL*Plus script that would do what you want.

drop table results;

create table results (id number, descr varchar2(50));

INSERT INTO results values (1, '20061025AUD0001-1');
INSERT INTO results values (2, '20061025USD0005-1');
INSERT INTO results values (3, '20061025USD0001-2');
INSERT INTO results values (4, '20061025GBP0007-4');
INSERT INTO results values (5, '20061025AUD0001-3');
INSERT INTO results values (6, '20061025GBP0007-3');
INSERT INTO results values (7, '20061025AUD0001-4');
INSERT INTO results values (8, '20061025GBP0007-2');
INSERT INTO results values (9, '20061025USD0005-3');
INSERT INTO results values (10, '20061025AUD0001-2');
INSERT INTO results values (11, '20061025GBP0007-1');
INSERT INTO results values (12, '20061025USD0005-4');
INSERT INTO results values (13, '20061025AUD0000-7');
INSERT INTO results values (14, '20061025USD0000-7');
INSERT INTO results values (15, '20061025GBP0000-7');

commit;

set pages 100
col grouping noprint
break on grouping skip 1

select case when id in (13,1,10) then 1
            when id in (5,7) then 2
            when id in (15,11,8) then 3
            when id in (6, 4) then 4
            when id in (14, 2, 3) then 5
            when id in (9, 12) then 6
            else 7 end||substr(descr, 1, 11) grouping,
            id, descr
from results
order by grouping,
case when id in (13,5,15,6,14,9) then 1
     when id in (1,7,11,4,2,12) then 2
     when id in (10,8,3) then 3
     else 500
end
/
Previous Topic: want to get number of sundays in a month
Next Topic: Help with Date error
Goto Forum:
  


Current Time: Sun Dec 04 02:22:52 CST 2016

Total time taken to generate the page: 0.04772 seconds