Is this SQL the most efficient way
We have a table that converts SAT scores into ACT scores using a year. if the data changes in the future we would add the new scores along with the year the scores change. We need to pass in a year and sat score and return the correct act score.

sample data with three rows would be

act sat year

28 1010 1998

29 1010 2012

30 1010 2015

If I pass in a SAT score of 1010 and a year of 2014 I should return an act score of 29 back.


I wrote the following SQL statement that works.  We are using Db2 is the reason for the FETCH FIRST ROW ONLY

select act, RANK() OVER(ORDER BY year DESC) from keessattbl where sat = 1010 and INT(year)<= 2014 FETCH FIRST ROW ONLY

Is this the most efficient way to handle this.

Thanks in advance Doug
Is this SQL the most efficient way
===

We have a table that converts SAT scores into ACT scores using a year. if the data changes in the future we would add the new scores along with the year the scores change. We need to pass in a year and sat score and return the correct act score.

sample data with three rows would be

act sat year

28 1010 1998

29 1010 2012

30 1010 2015

If I pass in a SAT score of 1010 and a year of 2014 I should return an act score of 29 back.


I wrote the following SQL statement that works.  We are using Db2 is the reason for the FETCH FIRST ROW ONLY

select act, RANK() OVER(ORDER BY year DESC) from keessattbl where sat = 1010 and INT(year)<= 2014 FETCH FIRST ROW ONLY

Is this the most efficient way to handle this.

Thanks in advance Doug