Find the top value for each parent

Question

I'm sure this is a common request but I wouldn't know how to ask for it formally.

I encountered this a long time ago when I was in the Army. A soldier has multiple physical fitness tests but the primary test that counts in the most recent. The soldier also has multiple marksmanship qualifications but only the most recent qualification to the weapon assigned is significant.

How do you create a view that itemizes the most significant child of the parent?

Answer 1

Use:

  SELECT p.*, x.*
    FROM PARENT p
    JOIN CHILD x ON x.parent_id = p.id
    JOIN (SELECT c.id,
                 c.parent_id,
                 MAX(c.date_column) AS max_date
            FROM CHILD c
        GROUP BY c.id, c.parent_id) y ON y.id = x.id
                                     AND y.parent_id = x.parent_id
                                     AND y.max_date = x.date

Assuming SQL Server 2005+:

WITH summary AS (
     SELECT p.*, 
            c.*, 
            ROW_NUMBER() OVER PARTITION BY p.id 
                                  ORDER BY c.date DESC) AS rank
       FROM PARENT p
       JOIN CHILD c ON c.parent_id = p.id)
SELECT s.*
  FROM summary s
 WHERE s.rank = 1

Answer 2

Although I'm not quite sure what you are implying by "itemizing", you can do something like so:

Select ..
From Soldier
    Left Join FitnessTest
        On FitnessTest.SoldierId = Soldier.Id
            And FitnessTest.TestDate =  (
                                        Select Max(FT1.TestDate)
                                        From FitnessTest As FT1
                                        Where FT1.SoldierId = FitnessTest.SoldierId
                                        )
    Left Join MarksmanshipTest
        On MarksmanshipTest.SoldierId = Soldier.Id
            And MarksmanshipTest.TestDate = (
                                            Select Max(MT1.TestDate)
                                            From MarksmanshipTest As MT1
                                            Where MT1.SoldierId = MarksmanshipTest.SoldierId
                                            )

This assumes that a solider cannot have two test datetime values for a fitness test or a marksmanship test.

Answer 3

No significant differnce from previous two answer but a little more detail perhaps:

create table soldier ( soldierId int primary key,
    name varchar(100) )
create table fitnessTest ( soldierId int foreign key references soldier,
    occurred datetime, result int )
create table marksmanshipTest ( soldierId int foreign key references soldier,
    occurred datetime, result int )

;with
mostRecentFitnessTest as
(
    select
        fitnessTest.soldierId,
        fitnessTest.result,
        row_number() over (order by occurred desc) as row
    from fitnessTest
),
mostRecentMarksmanshipTest as
(
    select
        marksmanshipTest.soldierId,
        marksmanshipTest.result,
        row_number() over (order by occurred desc) as row
    from marksmanshipTest
)
select
    soldier.soldierId,
    soldier.name,
    mostRecentFitnessTest.result,
    mostRecentMarksmanshipTest.result
from soldier
    left outer join mostRecentFitnessTest on
        mostRecentFitnessTest.soldierId = soldier.soldierId
        and mostRecentFitnessTest.row = 1
    left outer join mostRecentMarksmanshipTest on
        mostRecentMarksmanshipTest.soldierId = soldier.soldierId
        and mostRecentMarksmanshipTest.row = 1