SQLTeam.com | Weblogs | Forums

How to choose the right row


I'm thinking I need a case statement but I'm not sure where to put it. So far I have:

CASE WHEN ISNUMERIC(a.grade) = 1 THEN b.GradeType = 'Numeric'
else b.GradeType = 'Alpha'

I have two tables: table A has studentID, class, and grade. Table B has GradeType and Average.

Table A
StudentID   class   grade
125           003      75
456           004      82
854           005      A

Table B
GradeType   Average
Numeric        89
Alpha          4

GradeType is either Numeric or Alpha. My problem is some grades are Numeric and some are Alpha so when a grade is Numeric (or Alpha) it needs to also pick up what ever the average is for Numeric (or Alpha). I'm not sure how to accomplish this. I was thinking a case statement should be in either the from in the join or the where clause. If anyone could point me in the right direction. Thanks!


First off, it is considered bad practice to mix data types in one column. It would be better to have two columns: one for numeric grades and another for alphabetic grades. Actually the second column can be computed from the first.

Here's an example of a way to do that:

declare @TableA table (StudentID int,   class char(3),   grade_numeric int, grade_alpha char(2))

insert into @TableA(StudentID, class, grade_numeric, grade_alpha) values
--StudentID   class   grade
(125, '003', 75, null),
(456, '004', 82, null),
(854, '005', null, 'A')

with cte as
select StudentID, class
     , case when grade_numeric is null and grade_alpha is not null
          then case grade_alpha
               when 'A' then 98
               when 'B' then 88
               when 'C' then 78
               when 'D' then 68
               when 'E' then 55
               else 0
          else grade_numeric
      end as grade_numeric
     , case when grade_numeric is not null and grade_alpha is null
          then case 
               when grade_numeric > 92 then 'A'
               when grade_numeric > 82 then 'B'
               when grade_numeric > 72 then 'C'
               when grade_numeric > 62 then 'D'
               when grade_numeric > 52 then 'E'
               else 'F'
          else grade_alpha
      end as grade_alpha
from @TableA

select * from cte


Not quite what I'm looking for but, thanks! I have no control over the database or what the teachers put into it. I just need the corresponding average to whether its a numeric grade or an alpha grade. I have updated it to:

	CASE WHEN ISNUMERIC(a.grade) = 1 THEN (select b.gradetype from table b where b.GradeType='Numeric')
else (select b.gradetype from table b where b.GradeType='Alpha')

Its in the select statement but now I get the error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


You should scold your teachers for giving you a bad design to work with!

However, you can use what I posted to build your final query showing the averages.


Note that ISNUMERIC is not reliable. Better to use

NOT LIKE '%[^0-9]%'



Thanks! I will try this.