Returning the max value from a table

[newbie87]

Hi,

Does anybody know how to find the highest Value in a column and return all data related to that highest value?

I have a table called Details:
Name
Age
Gender
Address1
PostCode
Country

I know how to return the highest value in a column..

SELECT Max(Age) As Age From Details;

But I need it to show all columns relating to the highest Age. I've tried the following but its showing an error message "You tried to execute a query that does not include the specified expression 'Name, Gender etc'as part of an aggregate function"

SELECT Name, Max(Age) As Age, Gender, Address1, Postcode, Country From Details;

Does anybody have any idea's,

many thanks in advance

 

[Brianwarnock]

You join that query back to the table on age inner join maxage and select all other data in a second query

Brian

 

[newbie87]

Hi,

Many thanks for your quick response.

I have now worked this out as it is as follows:

SELECT Name, Age, Gender, Address1, PostCode, Country
FROM Details
where Age = (SELECT Max(Details.Age) AS Age
FROM Details);

I want to take it one step further, for example if there are two Johns and two Edwards, how would I find the max age of both names?

For example..
John 19
John 21
Edward 15
Edward 24

so i want to return all columns with the highest ages belong to edward and john?

Many thanks

 

[Brianwarnock]

First point don't use name as an field name as it is a reserved word and will cause you problems.

If you had tackled the problem as I suggested with 2 queries then you would simply group by the name field in the first query and join the query on the name and age fields to the table in the second query. The criteria can be applied in either query.

Brian