MAX() function

[deolalkar_pooja]

Hi to all,
I created following table

===================================================

create table man(id integer, name varchar(20), city varchar(20), age integer, dept integer)

===================================================

I want name and maximum age of person residing in 'mumbai'. I wrote following query, but got the error like 'can not include name with max() fuction since it is not the part of it and there is no GROUP BY clause'.

==================================================

SELECT name, MAX(age) FROM man where city='mumbai'

==================================================

It displays the age qhen I ran follo. query.

==================================================

SELECT MAX(age) FROM man where city='mumbai'.

=================================================

How can I get Both i.e. name and age.

[nonshatter]

SELECT MAX(age), name FROM man WHERE city='mumbai';

should work?

[deolalkar_pooja]

Thanks, I will do it.

[smantscheff]

Do you want to know the name of the Mumbai person of maximum age? Then you have to group by age, select the maximum of it and find a person of that same age:

select name from man m1 where m1.city = 'Mumbai' and m1.age = (select max(m2.age) from man m2);

Or do you want to find the oldest person in Mumbai?

select name from man m1 where m1.city = 'Mumbai' and m1.age = (select max(m2.age) from man m2 where m2.city = 'Mumbai');