Postgres how to return values in between Min column and Max column

Question

totally new in learning postgres/SQL I have a table called error_table that looks like this:

     id       min     max       error_percent

    10R46   1         25          9
    10R46   25        50          9
    100R91  50        100         4.5
    10R91   100       200         4.6
    10M95   200       300         5.5

but I am having a hard time generating a query on how to do this dynamically when my input number is 160, I would like to return the error_percent value 4.6 because 160 sits between the min column 100 and max column 200.

So far, here is what I am in my query

SELECT error_percent FROM error_table WHERE min <= 160

Now the problem is, I cannot use max <=160. How will I able to dynamically query the corresponding max value of returned min result and use it as my max <= returned max?

IF you have a better solution than mine, I appreciate the share.

I don't understand your question, seems like a simple where min <= 160 and max > 160? What exactly do you mean with "and use it as my max <= returned max"? — user330315
– user330315, Commented Oct 9, 2020 at 7:11
Your data is inconsistent. Do you want two rows returned for 50? Or is the max not inclusive? — Gordon Linoff
– Gordon Linoff, Commented Oct 9, 2020 at 12:20

Tim Biegeleisen · Accepted Answer · 2020-10-09 07:14:50Z

1

Use a range comparison:

SELECT error_percent
FROM error_table
WHERE min < 160 AND max >= 160;

This assumes that your ranges are open on the minimum side and closed on the maximum side. If not, then the above range comparison would need to be changed.

edited Oct 9, 2020 at 7:14

answered Oct 9, 2020 at 2:49

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

Alternatively, one could use where int4range(min, max, '[)') @> 160

Liu Jin Long Over a year ago

Thanks for this suggestion mate, a build-in function for postgres

Collectives™ on Stack Overflow

Postgres how to return values in between Min column and Max column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related