TransWikia.com

First negative number row in Oracle

Stack Overflow Asked by user10916118 on November 24, 2021

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

3 Answers

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.

Answered by casenonsensitive on November 24, 2021

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;

Answered by Gordon Linoff on November 24, 2021

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

Answered by The Impaler on November 24, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP