0

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.

3
Contributors
3
Replies
4
Views
6 Years
Discussion Span
Last Post by smantscheff
0

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');
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.