-
Thanks a lot. I was about to post the sorting order
Order by date,time
Sorry, i did not included Date as a column in my example above.
-
Is it a single date column?
Or
You store the time separately?
Tamil
-
The table has,
Date,Time,SubType,Value as columns
3/1/2006,7:00a.m,a,3
3/1/2006,7:00a.m,a,1
3/1/2006,7:00a.m,a,1
3/1/2006,7:03a.m,a,3
3/1/2006,7:03a.m,a,4
3/1/2006,7:03a.m,a,5
3/1/2006,7:06a.m,a,6
3/1/2006,7:06a.m,a,1
3/1/2006,7:06a.m,a,2
3/1/2006,7:09a.m,a,3
3/1/2006,7:09a.m,a,5
3/1/2006,7:09a.m,a,4
3/1/2006,7:12a.m,b,3
3/1/2006,7:12a.m,b,2
3/1/2006,7:12a.m,b,1
-
Sorting by date/time (why don't you use an Oracle DATE, which includes both?) will not be sufficient to get deterministic results. As you can see by the following possible retrieval orders:
Code:
3/1/2006,7:09a.m,a,1
3/1/2006,7:09a.m,a,2
3/1/2006,7:09a.m,a,3
3/1/2006,7:09a.m,a,4
3/1/2006,7:09a.m,a,5
3/1/2006,7:09a.m,a,6
3/1/2006,7:09a.m,a,7
3/1/2006,7:09a.m,a,8
3/1/2006,7:09a.m,a,9
and
3/1/2006,7:09a.m,a,3
3/1/2006,7:09a.m,a,4
3/1/2006,7:09a.m,a,1
3/1/2006,7:09a.m,a,5
3/1/2006,7:09a.m,a,6
3/1/2006,7:09a.m,a,2
3/1/2006,7:09a.m,a,7
3/1/2006,7:09a.m,a,8
3/1/2006,7:09a.m,a,9
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Here is one of the possible solutions.
(Note that for the simplicity of this case I deliberately made the data in the TIME column unique, so that we don't have a unique sorting problem that DaPi is refering to in the previous post.)
Code:
SQL> select * from tab1 order by time;
TIME SUBTYPE VALUE
-------- ------- ----------
07:00:00 a 2
07:00:20 a 0
07:00:40 a 5
07:03:00 a 3
07:03:20 a 2
07:03:40 a 1
07:06:00 a 3
07:06:20 a 3
07:06:40 a 4
07:09:00 b 5
07:09:20 b 2
07:09:40 b 1
12 rows selected.
SQL> select
2 time, subtype, value,
3 case when preceding = 3 or following = 3 then 'y' end as flag
4 from (select
5 time, subtype, value, indicator3, rownum,
6 count(indicator3) over(order by rownum rows between 2 preceding and current row) as preceding,
7 count(indicator3) over(order by rownum rows between current row and 2 following) as following
8 from (select
9 time, subtype, value,
10 case when value >= 3 then 1 end as indicator3
11 from tab1
12 order by time
13 )
14 );
TIME SUBTYPE VALUE FLAG
-------- ------- ---------- ----
07:00:00 a 2
07:00:20 a 0
07:00:40 a 5
07:03:00 a 3
07:03:20 a 2
07:03:40 a 1
07:06:00 a 3 y
07:06:20 a 3 y
07:06:40 a 4 y
07:09:00 b 5 y
07:09:20 b 2
07:09:40 b 1
12 rows selected.
SQL>
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Thanks Modic.
Even the time column is not unique, I can generate artifical unique key using rownum.
Below is the SQL that did not use analytical function.
Code:
SQL> select * from test ;
RTIME T VALUE
---------- - ----------
7.00 a 2
7.00 a 0
7.00 a 5
7.03 a 2
7.06 a 3
7.06 a 3
7.06 a 4
7.09 b 5
7.09 b 2
7.09 b 1
10 rows selected.
select distinct *
from (
select *
from ( select rn, rtime, least(3,value) leastval
from (select rownum rn, a.*
from test a order by rtime) ) iv1
where leastval = 3
and iv1.leastval = ( select leastval
from ( select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv2
where iv2.rn +1 = iv1.rn
)
and iv1.leastval = ( select leastval
from ( select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv3
where iv3.rn +2 = iv1.rn
)
union all
select *
from ( select rn, rtime, least(3,value) leastval
from (select rownum rn, a.*
from test a order by rtime) ) iv1
where leastval = 3
and iv1.leastval = ( select leastval
from ( select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv2
where iv2.rn -1 = iv1.rn
)
and iv1.leastval = ( select leastval
from ( select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv3
where iv3.rn +1 = iv1.rn
)
union all
select *
from ( select rn, rtime, least(3,value) leastval
from (select rownum rn, a.*
from test a order by rtime) ) iv1
where leastval = 3
and iv1.leastval = ( select leastval
from ( select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv2
where iv2.rn -1 = iv1.rn
)
and iv1.leastval = ( select leastval
from ( select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv3
where iv3.rn -2 = iv1.rn
)
)
/
RN RTIME LEASTVAL
---------- ---------- ----------
5 7.06 3
6 7.06 3
7 7.06 3
8 7.09 3
I will rewrite it using analytical function.
Tamil
-
Originally Posted by tamilselvan
Even the time column is not unique, I can generate artifical unique key using rownum.
Hi Tamil,
That's still not going to resolve the ordering problem in general - rownum is defined at the level of the result of a select, which may be effectively random inside one value of date/time. I suspect the OP will have to introduce a sequence number at the point at which the rows are inserted in the table, to have a bomb-proof solution.
If an update is required (as I suspect) then it could be done via ROWID:
Update myTable Set flag='Y'
Where rowid in
(a version of Juri's select statement, including rowid)
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
That's still not going to resolve the ordering problem in general - rownum is defined at the level of the result of a select, which may be effectively random inside one value of date/time.
I thought about that. That's why I asked the original poster about the sorting order.
We can assign the rownum only on the sorted result set. In this case we can get what we want.
Using rowid for update. You are right. If we want to update another column in the table, then we must use rowid for update.
Tamil
-
Thanks everybody for the response. Can I suggest the DBA team to make the two columns Date and Time into one column as Date? In this way we can sort by Date.
-
Originally Posted by rajan1
Thanks everybody for the response. Can I suggest the DBA team to make the two columns Date and Time into one column as Date? In this way we can sort by Date.
You are right. Then add date col in the order by clause.
OR
Even after you combine those 2 columns, they will not form a unique key. I suggest you should add a running seq col into the table, and make it as PK.
Tamil
Last edited by tamilselvan; 04-06-2006 at 09:59 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|