Web Development

Ask something
Question Locked

sql max date problem

0 helpful
4 replies

by iwparisi

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Hi I have two tables professor and exam. The professor table has: (id, year-name and degree) fields. The year table has : (year-name, start-date and end-date) fields. i want to know how i can select the professor degree who have the MAX start-date. Thank you!!

4 replies

by tony hopkinson

Select p.Id,p.YearName,p.degree
From ProfessorTable p,
(Select Year-Name,Max(Start-Date) as
LatestStart From YearTable) latest
Where p.YearName = latest.YearName

That will give you all the professors with the latest startdate for each year-name. That there is only one is an assumption not born by the schema you provided.

PS take the hyphens out of the field names, use legal identifiers, saves all sort of irritating difficulties.

by iwparisi

Thank's for your help Tony, i am really thankful. But when i try to run the sql query in Access <> show me an error <>

by tony hopkinson

Access, oh dear I was doing proper SQL.

In access I beleive you'd create one query for

Select Year-Name,Max(Start-Date) as
LatestStart From YearTable) latest
Where p.YearName = latest.YearName

call it Q_Latest or something

Then create another and join it to the professors table by year-name.

This discussion is closed to new replies.