Question

Locked

sql max date problem

By iwparisi ·
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!!

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Well assuming year-name is a reasonable link

by Tony Hopkinson In reply to sql max date problem

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.

Collapse -

ty but...

by iwparisi In reply to Well assuming year-name i ...

Thank's for your help Tony, i am really thankful. But when i try to run the sql query in Access <<Select Year-Name,Max(Start-Date) as
LatestStart From YearTable;>> show me an error <<You tried to execute a query that does not include the specified expression 'Year-Name' as a part of an agreed function.>>

Collapse -

Hmm you appear to have broke TR with that reply...

by Tony Hopkinson In reply to Well assuming year-name i ...

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.

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums