SQL: NULL placeholder values in a TOP 10 list that returns less than ten values

Question

I have written a query that returns a resultset of the top 10 values. This works great, but where a resultset returns a list of values that is smaller than 10 items then I would like the query to return placeholder values to pas the resultset out to ten values i.e. NULLS.

Current resultset:

SENDBottom10Prog    CFC 11  Business    Surname1    Forename1   11MO    50      2   4   U   -4.00
SENDBottom10Prog    CIN 11  Business    Surname2    Forename2   11MO    86.9    0   4   2   -2.00
SENDBottom10Prog    N   11  Business    Surname3    Forename3   11MC    100     0   3   2   -1.00
SENDBottom10Prog    N   11  Business    Surname4    Forename4   11MO    97.62   5   4   6   2.00

Desirable resultset:

SENDBottom10Prog    CFC   11    Business    Surname1    Forename1   11MO    50      2    4    U     -4.00
SENDBottom10Prog    CIN   11    Business    Surname2    Forename2   11MO    86.9    0    4    2     -2.00
SENDBottom10Prog    N     11    Business    Surname3    Forename3   11MC    100     0    3    2     -1.00
SENDBottom10Prog    N     11    Business    Surname4    Forename4   11MO    97.62   5    4    6      2.00
NULL                NULL  NULL  NULL        NULL        NULL        NULL    NULL    NULL NULL NULL   NULL
NULL                NULL  NULL  NULL        NULL        NULL        NULL    NULL    NULL NULL NULL   NULL
NULL                NULL  NULL  NULL        NULL        NULL        NULL    NULL    NULL NULL NULL   NULL
NULL                NULL  NULL  NULL        NULL        NULL        NULL    NULL    NULL NULL NULL   NULL
NULL                NULL  NULL  NULL        NULL        NULL        NULL    NULL    NULL NULL NULL   NULL
NULL                NULL  NULL  NULL        NULL        NULL        NULL    NULL    NULL NULL NULL   NULL

Here is my code so far:

DECLARE
@AcademicYear varchar(9) = '2019/2020',
@Collection varchar(50) = 'autumn',
@StuYear       VARCHAR(2) = '11';

Select 'SENDBottom10Prog' as List, SG, Year, Subject, Surname, Forename, Form, [AM/PM], Behaviour, FFT20, Prediction, Residual

From (

SELECT s.sg, s.Year,r.Subject as 'Subject', s.Surname, s.Forename, s.Form,s.attendance as 'AM/PM', s.behaviour, r.Target as FFT20, r.Prediction, g3.Points-g2.Points as Residual, g3.graderank, row_number() over(partition by r.subject order by s.year, r.subject, cast(g3.Points-g2.Points as decimal) asc, g3.graderank asc, Attendance asc, Behaviour, s.Surname, s.Forename) as rn

FROM Results r
LEFT JOIN Grades g1
  ON r.Result = g1.Grade
LEFT JOIN Grades g2
  ON r.Target = g2.Grade
LEFT JOIN Grades g3
  ON r.Prediction = g3.Grade
LEFT JOIN students s
  ON r.UPN = s.UPN

WHERE r.AcademicYear = @AcademicYear
AND s.AcademicYear = @AcademicYear
AND r.Collection = @Collection
AND s.year=@StuYear
AND SEND='Y'
AND s.NotIncluded = 'N'


) as T
where T.rn <=10;

score 2 · Answer 1 · answered Jan 19 '20 at 13:18

If you want to get exactly ten rows, but don't have them, then you need to construct them. Here is one method:

with t as (
      < your query here >
     )
select t.*
from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
     ) v(n) left join
     t
     on t.rn = v.n
order by v.n;

SQL: NULL placeholder values in a TOP 10 list that returns less than ten values

1 Answers1