first row oracle机制,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

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