0

I have a table in the db ,used as lookup to see what score a certain value has. The table

 5     1

10     2

15    3

If given an input, example 6, how do I query the table in Java and see that 6 is in between 5 and 10..so its score is 2. if it's 12. It's between 10 and 15 and the score is 3.

Or maybe instead of a table in the database, I could save the lookup values in a class ? Is there a more efficient way to do this ?

  • 1
    Welcome to SO! While there are many SO users that could write that code, SO is not a code writing service, but a question/answer forum. Make an attempt to solve your problem, share your code and then ask a specific question. – DSway Oct 01 '17 at 05:44
  • You can use a TreeList to do this. Read the javadocs. – Stephen C Oct 01 '17 at 07:04
  • DSWay is absolutely right. On top of what he says, I want to add that if you're a Java beginner, dealing with the database should maybe not be the first thing to try and learn. Instead, try to solve your problems with "[constants](https://stackoverflow.com/q/66066/2525313)" or [enums](https://www.sitepoint.com/fundamentals-of-java-enum-types-tutorial/). – Nicolai Parlog Oct 01 '17 at 07:07

1 Answers1

0

In this case you need to get the max value of field1 that is smaller than the current record's field1 value and check if the searched value falls between these two values. If yes, return field2. Assuming that there is no overlapping ranges, you can use the following query:

select field1, (select max(field1) from yourtable t2 where t2.field1<t1.field1) as lowerfield1, field2
from yourtable t1
where ...<fíeld1 and ...>=(select max(field1) from yourtable t2 where t2.field1<t1.field1)

The searched value must be in place of the ....

Shadow
  • 33,525
  • 10
  • 51
  • 64