CODEWITHSUNDEEP
sqlpostgresql
Zach
Zach

Reputation: 650

Finding the maximum value between a given interval

Let's say I have a table like so, where the amount is some arbitrary amount of something(like fruit or something but we don't care about the type)

row  |  amount
_______________

1    |  54
2    |  2
3    |  102
4    |  102
5    |  1

And I want to select the rows that have the maximum value within a given interval. For instance if I was only wanting to select from rows 2-5 what would be returned would be

row  |  amount
_______________

3    |  102
4    |  102

Because they both contain the max value within the interval, which is 102. Or if I chose to only look at rows 1-2 it would return

row  |  amount
_______________

1    |  54

Because the maximum value in the interval 1-2 only exists in row 1

I tried to use a variety of:

amount= (select MAX(amount) FROM arbitraryTable)

But that will only ever return

row  |  amount
_______________

3    |  102
4    |  102

Because 102 is the absolute max of the table. Can you find the maximum value between a given interval?

Upvotes: 0

Views: 1095

Answers (2)

Adam
Adam

Reputation: 5599

You can use a subquery to get the max value and use it in WHERE clause:

SELECT
    row,
    amount
FROM
    arbitraryTable
WHERE
    row BETWEEN 2 AND 5 AND
    amount = (
        SELECT
            MAX(amount)
        FROM
            arbitraryTable
        WHERE
            row BETWEEN 2 AND 5
    );

Just remember to use the same conditions in the main and sub query: row BETWEEN 2 AND 5.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

I would use rank() or max() as a window function:

select t.row, t.amount
from (select t.*, max(amount) over () as maxamount
      from t
      where row between 2 and 4
     ) t
where amount = maxamount;

Upvotes: 0

Related Questions