Home » SQL & PL/SQL » SQL & PL/SQL » Filtering (11.1 g)
| Filtering [message #568663] |
Mon, 15 October 2012 10:54  |
 |
primer2020
Messages: 32
Registered: August 2012
|
Member |
|
|
Hello,
I have the following table:
{code}
create table test
(
id number,
class varchar (10),
value number,
year number
);
insert into test
values (300,
'AA',
100,
1997);
insert into test
values (300,
'AA',
1700,
1998);
insert into test
values (300,
'AA',
2900,
1999);
insert into test
values (300,
'AA',
0,
2000);
insert into test
values (300,
'AA',
0,
2001);
insert into test
values (200,
'BB',
0,
1997);
insert into test
values (200,
'BB',
2600,
1998);
insert into test
values (200,
'BB',
3400,
1999);
insert into test
values (200,
'BB',
100,
2000);
insert into test
values (200,
'BB',
0,
2001);
insert into test
values (100,
'CC',
0,
1997);
insert into test
values (100,
'CC',
0,
1998);
insert into test
values (100,
'CC',
0,
1999);
insert into test
values (100,
'CC',
500,
2000);
insert into test
values (100,
'CC',
0,
2001);
select id,
class,
value,
year,
case value when 0 then 0 else 1 end as bool
from test order by id, class, year;
ID CLASS VALUE YEAR BOOL
100 CC 0 1997 0
100 CC 0 1998 0
100 CC 0 1999 0
100 CC 500 2000 1
100 CC 0 2001 0
200 BB 0 1997 0
200 BB 2600 1998 1
200 BB 3400 1999 1
200 BB 100 2000 1
200 BB 0 2001 0
300 AA 100 1997 1
300 AA 1700 1998 1
300 AA 2900 1999 1
300 AA 0 2000 0
300 AA 0 2001 0
{code}
I want use only SQL to filter out data base on BOOL vector for each group of ID and CLASS. Please note that the data is in the unpivot form and do not want to pivot it.
For example: ID=100 AND CLASS =CCC ===> BOOL verctor (1,1,1,0,0)
Filter out if Vector starts with 1 and ends with 0
or Vector all 0
Desired result:
ID CLASS VALUE YEAR BOOL
100 CC 0 1997 0
100 CC 0 1998 0
100 CC 0 1999 0
100 CC 500 2000 1
100 CC 0 2001 0
200 BB 0 1997 0
200 BB 2600 1998 1
200 BB 3400 1999 1
200 BB 100 2000 1
200 BB 0 2001 0
Thank you
[Updated on: Mon, 15 October 2012 11:20] Report message to a moderator |
|
|
|
| Re: Filtering [message #568665 is a reply to message #568663] |
Mon, 15 October 2012 11:58  |
 |
Michel Cadot
Messages: 54712
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
SQL> with
2 data as (
3 select id, class, value, year,
4 first_value (value) over
5 (partition by id, class order by year
6 rows between unbounded preceding and unbounded following)
7 fval,
8 last_value (value) over
9 (partition by id, class order by year
10 rows between unbounded preceding and unbounded following)
11 lval,
12 max (value) over
13 (partition by id, class order by year
14 rows between unbounded preceding and unbounded following)
15 mval
16 from test
17 )
18 select id, class, value, year
19 from data
20 where ( fval = 0 or lval != 0 )
21 and mval != 0
22 order by id, class, year
23 /
ID CLASS VALUE YEAR
---------- ---------- ---------- ----------
100 CC 0 1997
100 CC 0 1998
100 CC 0 1999
100 CC 500 2000
100 CC 0 2001
200 BB 0 1997
200 BB 2600 1998
200 BB 3400 1999
200 BB 100 2000
200 BB 0 2001
Regards
Michel
|
|
|
|
|
|
|
|
| Re: Filtering [message #568680 is a reply to message #568678] |
Mon, 15 October 2012 13:53 |
Solomon Yakobson
Messages: 1443
Registered: January 2010
|
Senior Member |
|
|
primer2020 wrote on Mon, 15 October 2012 14:46Hi,
How can control this statment so I get the min of value where year > minimum year and < maximum year.
min(case when year > 1997 and year < 2001 then value end) over(partition by id) mval
SY.
[Updated on: Mon, 15 October 2012 13:54] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Wed Jun 19 23:11:28 CDT 2013
Total time taken to generate the page: 0.13052 seconds
|
] 
Members
Search
Help
Register
Login
Home
