Applying distinct criteria to only one field in the results of a query 1

[mas5]

I am trying to generate mailing labels for our school PTO. I want to generate one mailing label per family to go to the classroom of the oldest child at the school or even the first child in the list for the family.

I have two tables:

Family Table that contains a family id and other info
Student Table that contains the family id, student name, grade and classroom and other info.

The mailing labels need to look like the following:

Family Name
c/o Student Name
Classroom

When I join the two tables I get an entry for each student at the school, but I only want an entry for the oldest child in each family. Select distinct or distinctrow doesn't work because each child is distinct and I only want to apply distinct to the familyid.

The statement:

Select family.familyid, studentname, studentclass
from family, student
where family.familyid = student.familyid

yeilds one record for each child.

This statement

Select distinct family.familyid, studentname, studentclass
from family, student
where family.familyid = student.familyid

yields the same results. I want to apply distinct to the familyid only. Or just get the first student within each familyid.

Can I do this with a SQL statement only or do I need to first extract all the data, sort it and then programmatically skip the duplicate family records? I've tried various queries with subqueries, union queries (worked except for families with children in the same grade) but nothing seems to work.

Hope I'm explaining this sufficiently.

 

[PWise]

if you have a distinct id (or something similir) in students
you can make a sepreate query "SELECT Max(studentid) AS MaxOfid, student.familyid
FROM student
GROUP BY student.familyid;"
in second query select all fields you need from students table
inner join student.id on student.familyid
this will only give you one listing per family

 

[mas5]

Thank you!! It works!!