SQL make a query with new column values depending on fixed values

I have two tables table1 and table2

table1 has the columns:

id name  value
1  name1 0.5
2  name2 1.9
3  name3 2.6
4  name4 0.2
....
999 name999 4.7

table2 has the columns

id risk   value
1  low    0.5
2  medium 1.5
3  high   2.5

I want to create a result for example: (low >=0.5 and <1.5 , medium >=1.5 and <2.5, high >2.5开发者_JS百科) and from higher value to smaller value

1  name999 high
2  name3 high
3  name2 medium
4  name1 low

Addition

Another one question:

table1 has the columns:

id name  value
1  name1 0.5
2  name2 1.9
3  name3 2.6
4  name4 0.2
....
999 name999 4.7

suppose in column name we have test1 and test2 and we want in the result for test1 to always be very low and for test2 very high.So what will be the sql modification to have this result?

1  test2   very high
2  name999 high
3  name3 high
4  name2 medium
5  name1 low
6  test1 very low

do we have to edit table2 also for ordering the above result by risk?

SELECT a,id
     , a.name
     , b.risk 
FROM table1 AS a
  JOIN table2 AS b
    ON b.value = ( SELECT MAX(bm.value)
                   FROM table2 AS bm
                   WHERE bm.value <= a.value
                 )
ORDER BY a.value DESC

This would be much easier if you specify the upper and lower bounds in table2 instead of trying to perform some kind of nearest-number lookup. It could be as simple as

SELECT a.name, b.risk FROM table1 a
LEFT JOIN table2 b ON a.value BETWEEN b.lower AND b.upper

If you insist on using the nearest-number lookup, you can determine which value from table1 is closest to table2 by writing a select which includes ABS(a.value - b.value), ordering by that field and selecting the top 1, but there's no easy way to do the join at that point. You'll likely be inserting records into a temp table from within a cursor, selecting from the temp table and then dropping it - a big nasty mess that could be avoided by simply making one small change to your second table.