sql server - SQL - Retrieve Closest Lower Value -

sql server - SQL - Retrieve Closest Lower Value -

when column value not equal, retrieve closest lower pay value.

for instance: 10 yearsofservice should equal value 650.00; 14 yearsofservice equal value 840.00 in below incentive table,

id pay yearsofservice 1 125.00 0 2 156.00 2 3 188.00 3 4 206.00 4 5 650.00 6 6 840.00 14 7 585.00 22 8 495.00 23 9 385.00 24 10 250.00 25

i have tried several different approaches; including:

select top 1 (pay) incentivepay incentive yearsofservice = '10'

this works yearsofservice match.

with 10 yearsofservice:

resultset = [1 650.00]

any ideas?

please try:

select top 1 (pay) incentivepay incentive yearsofservice <= '10' order yearsofservice desc

sql sql-server sql-server-2008