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.
Printable View
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
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>
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.
I will rewrite it using 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
Tamil
Hi Tamil,Quote:
Originally Posted by tamilselvan
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)
I thought about that. That's why I asked the original poster about the sorting order.Quote:
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.
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.
You are right. Then add date col in the order by clause.Quote:
Originally Posted by rajan1
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