id | amount
-----------
1  | 100  |
2  | 0    |
3  | 0    |
4  | 200  |
5  | 0    |
6  | 0    |

I have this table in which the value column will have frequent values greater than 0. I am trying to figure out a SQL query that will print values between the first greater than 0 value and the second greater than 0 value.

for the above table, the output will be

id | amount
-----------
1  | 100  |
2  | 0    |
3  | 0    |

I am not getting what kind of condition will be applied to achieve this. I have tried the Group By but it only counts the total number of the same value.

I also thought of giving each value a unique number such as below

id | Serial number | amount
---------------------------
1  |       1       | 100  |
2  |       1       | 0    |
3  |       1       | 0    |
4  |       1       | 200  |
5  |       1       | 0    |
6  |       1       | 0    |

But again I was unable to display the data with query since I am confused about how to do it.

CodePudding user response：

Something like this:

WITH v AS (
 SELECT *, SUM(amount) OVER(ORDER BY id ASC) sum_amount, MAX(amount) OVER(ORDER BY id ASC) max_amount
 FROM t
)
SELECT id, amount
FROM v
WHERE sum_amount > 0 AND sum_amount = max_amount

Demo

Look at this query

 SELECT *, SUM(amount) OVER(ORDER BY id ASC) sum_amount, MAX(amount) OVER(ORDER BY id ASC) max_amount
 FROM t

With result set:

 ---- -------- ------------ ------------ 
| id | amount | sum_amount | max_amount |
 ---- -------- ------------ ------------ 
|  1 |    100 |        100 |        100 |
|  2 |      0 |        100 |        100 |
|  3 |      0 |        100 |        100 |
|  4 |    200 |        300 |        200 |
|  5 |      0 |        300 |        200 |
|  6 |      0 |        300 |        200 |
|  7 |      0 |        300 |        200 |
|  8 |    300 |        600 |        300 |
|  9 |      0 |        600 |        300 |
 ---- -------- ------------ ------------ 

SUM, MAX in window function will calculate the value up to the current row as per ORDER BY id ASC

• sum_amount > 0 finds the first row with amount greater than zero
• sum_amount = max_amount excludes another row with amount greater than zero and the rows after that, because when reaching such a row, the sum is always greater than the max

CodePudding user response：

Assuming that the data is sorted by id you can simply:

SELECT *
FROM t
WHERE id >= (SELECT id FROM t WHERE amount > 0 ORDER BY id LIMIT 0, 1)
AND   id <  (SELECT id FROM t WHERE amount > 0 ORDER BY id LIMIT 1, 1)

CodePudding user response：

I assume I don't know when the first positive value appears first. Using the window function and 'case when' logic I build a positive values counter by means of which I can pull out all the records between my x positive value (x representing position) and y positive value (y representing position appearing after x).

Demo

CREATE TABLE tbl (id INT PRIMARY KEY,amount INT NOT NULL);
INSERT INTO tbl VALUES
(1,0 ),(2,100),(3,0),(4,0),(5,200),(6,0),(7,0),(8,300),(9,0);
    

 SELECT 
 *, sum(amount_check) over (order by id asc)  as positive_value_counter
 FROM
    (SELECT *, 
    CASE WHEN amount > 0 then 1 else 0 end as amount_check
    FROM tbl) s   

 ---- -------- --------------- ------------------------ 
| id | amount | amount_check  | positive_value_counter |
 ---- -------- --------------- ------------------------ 
|  1 |      0 |        0      |        0               |
|  2 |    100 |        1      |        1               |
|  3 |      0 |        0      |        1               |
|  4 |      0 |        0      |        1               |
|  5 |    200 |        1      |        2               |
|  6 |      0 |        0      |        2               |
|  7 |      0 |        0      |        2               |
|  8 |    300 |        1      |        3               |
|  9 |      0 |        0      |        3               |
 ---- -------- --------------- ------------------------ 
 
SELECT *
FROM
    (SELECT *, sum(amount_check) over (order by id asc)  as positive_value_counter
        FROM
        (SELECT *, 
        CASE WHEN amount > 0 then 1 else 0 end as amount_check
        FROM tbl) s
    ) m
WHERE
positive_value_counter >= 1 and positive_value_counter < 2
              
 ---- -------- --------------- ------------------------ 
| id | amount | amount_check  | positive_value_counter |
 ---- -------- --------------- ------------------------ 
|  2 |    100 |        1      |        1               |
|  3 |      0 |        0      |        1               |
|  4 |      0 |        0      |        1               |
 ---- -------- --------------- ------------------------