CODEWITHSUNDEEP
sqloracle-database
user10916118
user10916118

Reputation:

First negative number row in Oracle

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

Upvotes: 0

Views: 829

Answers (3)

casenonsensitive
casenonsensitive

Reputation: 950

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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 <ordering col> asc
fetch first 1 row only;

Upvotes: 2

The Impaler
The Impaler

Reputation: 48770

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".

Upvotes: 1

Related Questions