Max() in sql query

Question

I have some records in my Table.

I want to select a record having Maximum Age

If i write below query it's working fine.

Select MAX(Age)
From Table

It's working fine. But If i write like this,

Select FirstName, LastName, MAX(Age)
From Table
Group By FirstName, LastName

It's Not Working(Showing all Records). How can i fix this ?

Answer 1

You can use a subquery to get the maximum Age and and compare the result on the outer query's age.

Select  *
From    TableName
WHERE   Age = (SELECT MAX(Age) FROM TableName)

Brief explanation, the use of GROUP BY in your query doesn't exactly do what you want because it is not a filtering operator and does only group non-aggregate columns. For instance you have two records which has the same first name and last name but with different age, the result will be the person with the greatest age because of the use of MAX().

Answer 2

Since with max(Age) you are going to get single record and FirstName,LastName has multiple records.

You are using this together hence it is creating ambiguity.

Select FirstName, LastName from From Table where Age = (SELECT MAX(Age) FROM Table)

Use this query.