Selecting row with max running total on one column less than a given value

Question

For example, for a table such as :

ID | col_a  | col_b  | col_c
=============================
 1 |5.0     |7.0     |3
 2 |3.0     |6.8     |5

I need to find the value of col_a / col_b for which the running total on col_c is less than a given value.

So far I have:

select MAX(running_total) as max FROM (select (col_a / col_b) as val, SUM(col_c)
OVER (ORDER BY value ROWS UNBOUNDED PRECEDING) as running_total FROM tableName)
WHERE running_total < 50;

This gives me the maximum running total but I also need the val (col_a/col_b) for the row where this running_total was achieved.

I am using Amazon Redshift for this query, which unlike mysql wont let me place val in the outer select statement, without adding a group by clause on val. I cant add the group by clause cause that would change the whole semantic of query.

I have found solution to similar problem - Fetch the row which has the Max value for a column

Mostly these solutions suggests, that we join with the same table and then match values for the column, but the running_total column is calculated and to do a join on it, I have to calculate it again ? which sounds fairly expensive.

score 2 · Accepted Answer · answered Dec 18 '13 at 02:21

You can do this. Window functions to the rescue.

Just add another layer of subquery that calculates the maximum running total on each row. Then use a where clause to get the row where they match:

select t.*
from (select t.*,
             max(running_total) over () as maxrt
      FROM (select (col_a / col_b) as val,
                   SUM(col_c) OVER (ORDER BY value ROWS UNBOUNDED PRECEDING
                                   ) as running_total
            FROM tableName
           ) t
      WHERE running_total < 50
     ) t
where running_total = maxrt;

Selecting row with max running total on one column less than a given value

1 Answers1