0

I would like to use the Lead function to get the closest value for a group
Below is some sample data from flx_alps_boundaries

Subject code Grade Score
20-BD-AC-AL 1 1.12
20-BD-AC-AL 2 1.03
20-BD-AC-AL 3 0.97
20-BD-AC-AL 4 0.92
20-BD-AC-AL 5 0.86
20-BD-AC-AL 6 0.84
20-BD-AH-AL 1 1.15
20-BD-AH-AL 2 1.10
20-BD-AH-AL 3 1.05
20-BD-AH-AL 4 1.00
20-BD-AH-AL 5 0.98
20-BD-AH-AL 6 0.96

I am calculating the score for a subject using a formula and getting the grade for the nearest matching score from the above table . eg if score is 0.95 for subject 20-BD-AC-AL the grade should be 4

This is my current sql

   select top 1 
          ab.alps_grade  as alps_grade,
          round( sum (actual_alps_points - expected_alps_points) 
                   / (count(reference) * 100)  + 1,2 ) as alps_score
   from   alps_cte 
   inner join [flx_alps_boundaries] ab 
           on alps_cte.course = ab.course_code
   where  ab.course_code in ('20-BD-AC-AL','20-BD-AH-AL')
   group by course,ab.alps_grade,ab.alps_score
   order by abs(round(sum(actual_alps_points 
                       - expected_alps_points) 
                       / (count(reference)*100)  + 1, 2) 
                       - ab.alps_score)

This query only returns one row. How do I use LEAD to get the appropriate grade for each subject's score?

Squirrel
  • 23,507
  • 4
  • 34
  • 32
Suzan Allen
  • 1
  • 1
  • 1
    `This query only returns one row`. You have a `TOP 1` there, have you tried removing that ? – Squirrel May 04 '22 at 09:39
  • @Squirrel When I remove the Top 1 from the sql I get several rows for each course code
    What I need is 1 row per course code
    The row I need for each code is the one with the appropriate grade for each course's score.
    The order by clause puts the appropriate grades for each of the 2 courses at the top of the list     – Suzan Allen May 05 '22 at 11:14
  • use `row_number() over (partition by course_code order by somecol)` There are lots of similar question in this site – Squirrel May 05 '22 at 12:34

0 Answers0