Retrieving range of values from a lookup table using SQL

Question

I am using SQL Server. I have two tables Result and Grading. Using T-SQL select statement, from the lookup table (Grading),I want to fill Grade column in Result table with the knowledge of range of values in Score column of lookup table. As shown below

Grading (lookup table)

Answer 1

Assuming Grading.score represents the lower boundary for the grade, you could do something like this:

SELECT
    student,
    score,
    grade_lookup.grade
FROM result r
    CROSS APPLY ( SELECT MIN(g.grade) AS grade
                    FROM
                        grading g
                    WHERE
                        g.score<=r.score) AS grade_lookup

A score under 33 won't get any grade assigned, so you may want to modify Grading table to include scores under 33.

UPDATE: This query won't work with TEXT datatype. You can avoid MIN() by using TOP 1 and ORDER BY:

SELECT
    student,
    score,
    grade_lookup.grade
FROM result r
    CROSS APPLY ( SELECT TOP(1) grade
                    FROM
                        grading g
                    WHERE
                        g.score<=r.score
                    ORDER BY score DESC) AS grade_lookup

That said, you should avoid using deprecated TEXT datatype. For long text, use VARCHAR(MAX). For something short like a grade ranging from A to F, a simple CHAR(1) is way more appropriate.