I create the following table.
create table man(id integer, name varchar(20), city varchar(20), age integer)
I want to find the name of the person with maximum age.
So, I ran following query,
select name, max(age) from man group by name;
It shows name of all person in ascending order with their age. What is going wrong?
If you want to obtain the person with the greater age, you can do :
select name, age from man order by age desc limit 1;
For a query working with SQL Server :
select top 1 name, age from man order by age desc;
Your query is doing something else. max uses your group clause to do its computation. Since you're grouping by name, max(age) is the maximal age for people with the same name.
I struggle to understand what you're doing here. A person can have more than a single age in your model?
Anyway. If you want the maximum age in the table man, you need to run:
select max(age) from man
Now who is this? If you add name to the query, the DB engine will return all names (since you asked for all of them, not a specific one; grouping won't help since most names will already be distince) combined with the max. value of the age column.
What you need is a subquery:
select m1.name
from man m1
where m1.age = (
select max(m2.age)
from man m2
)
This selects all elements where the age is the same as the highest age in the set.
select name. max(age) from man gruop by name;
Should be
SELECT name, max(age) FROM man GROUP BY name;
You could also try
SELECT name, age FROM man ORDER BY age DESC
Maybe a little more information about the results you were expecting would be helpfull?
SELECT NAME, AGE
FROM MAN
GROUP BY NAME, AGE
HAVING AGE = (SELECT MAX (AGE) FROM MAN)
