How do I select a given number of rows for one table for each parent primary key in another table in sql server 2012?

Question

I have two tables in sql. One is a table of test cases and the other is a table of test runs with a foreign key that links back to a test case. I want to get the most recent 10 test runs for each test case. I don't want to loop through if I don't have to, but I don't see any other way to solve this problem. What is the most effective way to handle this sort of thing in sql server?

Answer 1

The idea:

select
    ...
from <test cases> as tc
    outer apply (
        select top 10 *
        from <test runs> as tr
        where
            tr.<test case id> = tc.<id>
        order by tr.<date time> desc
    ) as tr

or, if you just need to get data from table:

;with cte_test_runs as (
   select
        *,
        row-Number() over(partition by <test case id> order by <date time> desc) as rn
    from <test runs>
)
select *
from cte_test_runs
where rn <= 10