How can I find the first row where the negative value starts in Oracle? Below is an example.
77
74
67
56
42
24
20
19
-17
-28
-31
-36
I would like to read the row -17 and do some operations on that row.
Any help is greatly appreciated. Thanks
# Answer 1
If you want the complete row, you can use:
select t.*
from t
where n < 0
order by n asc
fetch first 1 row only;
If you have another column that specifies the ordering of the rows, then:
select t.*
from t
where n < 0
order by asc
fetch first 1 row only;
# Answer 2
To select the maximum negative number you can do:
select max(n)
from t
where n < 0
Remember that in relational database tables, rows do not have inherent ordering. Therefore, in the absence of an ordering criteria, there's no such a thing as "first row where the negative value starts".
# Answer 3
Assuming you have a column for sorting, which defines the order of the rows, it could look like this:
with t as (
select 77 a, 1 row_order from dual union all
select 74 a, 2 row_order from dual union all
select 67 a, 3 row_order from dual union all
select 56 a, 4 row_order from dual union all
select 42 a, 5 row_order from dual union all
select 24 a, 6 row_order from dual union all
select 20 a, 7 row_order from dual union all
select 19 a, 8 row_order from dual union all
select -17 a, 9 row_order from dual union all
select -28 a, 10 row_order from dual union all
select -31 a, 11 row_order from dual union all
select -36 a, 12 row_order from dual
), t1 as (
select a, row_number() over (partition by case when a < 0 then 0 else 1 end order by row_order) rn from t
)
select * from t1 where rn = 1 and a < 0;
It's using a window function in order to determine the first rows (here for positive a's and negative a's)
Then it selects the first row encountered that is negative.