how works having max()?

[Mariellelis]

Hi!

First, excuse me, my english is not good at all.

So I try to explain :
I have 2 tables : "person" and "stay".
For one person, we can have one or more stay.
I want to select all the person and their last stay and I don't know how to do it.

I tried like that :
"select nom,numstay, placestay
from person,stay
where person.numperson=stay.numperson
group by person.numperson
having max(numstay);"
But I have no result.

So how can I do?

Thanks in advance for your help!

 

[carlrichey]

Your English is fine. I don't know the exact structure of your tables, but something like this should work:

select person.numperson, person.nom,
stay.placestay, max(stay.numstay)
from person, stay
where person.numperson = stay.numperson
group by person.numperson

Note that this will work fine in MySQL, however Oracle or SQL Server will have problems returning columns that are not in the "Group By" expression...

 

[Mariellelis]

Thanks for your answer.

While doing this, I have the placestay of the first numstay found, and not the one corresponding to the max numstay.

A not perfect solution would be to add a field max_numstay in the person table, but it's not really good...
Have you another idea?