Selecting the top 1 of one column, for each value of another column

Question

We have report cycles for our secondary school students. I am trying to write a query that will create a neat little table showing the most recent report cycle for each of our secondary school years.

I have found a way to show what I want, but it uses UNION and seems like there should be a much more efficient way of doing it.

So currently I am using this repeated 7 times, changing the WHERE clause so SchoolYear is changed to each of the ones we need it for (7 through to 13):

SELECT *
FROM 
    (SELECT TOP 1
    CycleAllocations.ReportCycle,
    CycleAllocations.SchoolYear,
    CycleDetails.ReportName
FROM CycleAllocations
LEFT OUTER JOIN CycleDetails
ON CycleAllocations.ReportCycle = CycleDetails.ReportCycle 
WHERE SchoolYear = 7
ORDER BY intReportCycle DESC) AS CYCLE7
UNION
SELECT *
FROM 
    (SELECT TOP 1
    CycleAllocations.ReportCycle,
    CycleAllocations.SchoolYear,
    CycleDetails.ReportName
FROM CycleAllocations
LEFT OUTER JOIN CycleDetails
ON CycleAllocations.ReportCycle = CycleDetails.ReportCycle 
WHERE SchoolYear = 8
ORDER BY intReportCycle DESC) AS CYCLE8
UNION... etc etc

This produces a table like this:

ReportCycle	SchoolYear	ReportName
173	7	Short Report March 2021
173	8	Short Report March 2021
173	9	Short Report March 2021
173	10	Short Report March 2021
174	11	Yr11 Mock Exams Jan 2021
172	12	Long Report March 2021
175	13	U6 Mock Exams Jan 2021

Is there a better way of writing this?

Answer 1

You can give this a try, it may need tweaking as you haven't shared your table schema or any sample data.

Using a row number function to number the rows ordered so that your top1 row gets the number 1 and repeating that for each "partition" which is each school year, then only return the rows with a number 1.

I've used table aliases too which makes the query more terse and easier to read.

    select ReportCycle, SchoolYear, ReportName from (
        select ca.ReportCycle, ca.SchoolYear, cd.ReportName, Row_Number() over (partition by schoolyear order by intReportCycle desc )
        from CycleAllocations ca
        left join CycleDetails cd on ca.ReportCycle = cd.ReportCycle 
    )x
    where rn=1
order by SchoolYear