complex sql query help needed

Question

I'm not sure how to write this query in SQL. there are two tables

**GroupRecords**
Id (int, primary key)
Name (nvarchar)
SchoolYear (datetime)
RecordDate (datetime)
IsUpdate (bit)

**People**
Id (int, primary key)
GroupRecordsId (int, foreign key to GroupRecords.Id)
Name (nvarchar)
Bio (nvarchar)
Location (nvarchar)

return a distinct list of people who belong to GroupRecords that have a SchoolYear of '2000'. In the returned list, people.name should be unique (no duplicate People.Name), in case of a duplication only the person who belong to the GroupRecords with the later RecordDate should be returned.

It would probably be better to write a stored procedure for this right?

Answer 1

Select Distinct ID 
From People 
Where GroupRecordsID In 
   (Select Id From GroupRecords
    Where SchoolYear = '2000/1/1')

This will produce a distinct list of those individuals in the 2000 class... but I don't understand what you're getting at with the cpmment about duplicates... please elaborate...

It reads as though you're talking about when two different people happen to have the same name you don't want them both listed... Is that really what you want?

Answer 2

MySQL specific:

SELECT *
FROM `People`
LEFT JOIN `GroupRecords` ON `GroupRecordsId` = `GroupRecords`.`Id`
GROUP BY `People`.`Name`
ORDER BY `GroupRecords`.`RecordDate` DESC
WHERE `GroupRecords`.`SchoolYear` = '2000/1/1'

Answer 3

people.name should be unique (no duplicate People.Name)

? Surely you mean no duplicate People.ID?

in case of a duplication only the person who belong to the GroupRecords with the later RecordDate should be returned.

There's the rub — that's the bit that it's not obvious how to do in plain SQL. There are a number of approaches to the “For each X, select the row Y with maximum/minimum Z” question; which work and which perform better depend on which database software you're using.

http://kristiannielsen.livejournal.com/6745.html has some good discussion of some of the usual techniques for attacking this (in the context of MySQL, but widely applicable).

Answer 4

This is untested, but it should do what is required in the question.

It selects all details about the person.

The subquery will make it match only the latest RecordDate for a single name. It will also look only in the right GroupRecord because of the Match between the ids.

SELECT
    People.Id,
    People.GroupRecordsId,
    People.Name,
    People.Group,
    People.Bio,
    People.Location

FROM
    People
    INNER JOIN GroupRecords ON GroupRecords.Id = People.GroupRecordsId

WHERE
    GroupRecords.SchoolYear = '2000/1/1' AND
    GroupRecords.RecordDate = (
        SELECT 
            MAX(GR2.RecordDate)
        FROM 
            People AS P2
            INNER JOIN GroupRecords AS GR2 ON P2.GroupRecordsId = GR2.Id
        WHERE
            P2.Name = People.Name AND
            GR2.Id = GroupRecords.Id
    )