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.

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

should work?

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');
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.