How to combine MAx statement with specific search?

Hello everyone!

I've got a table (of "Persons") with 2 columns: "Age" and "Name".
I make a procedure that get the MAX of "Age" of the table, like this:

SELECT
MAX (p.Age) MaxAge
FROM
Persons p

But I'll like to add a new column to my result table. That column corresponds to the "Age" of a person 'X'. 'X' is its 'Name' and i will pass it by parameter to the procedure.
Something like this:

SELECT

MAX (p.Age) MaxAge, --The highest age of the table
p.Age ConcretPersonAge -- The age of the person X

FROM

Persons p

That should returns a single row result table with the max of all 'Age" and the 'Age' of the person 'X'. Anyone can help me to do this?

Thank you to everyon, and a happy new year

Jonathan

You can use a subquery, a derived table or store the result in a variable.

i.e. the sub query one looks like this

select (select max(age) from person) as maxAge, age

from person

where name = @.name

|||

You can use the following query,

SELECT
(Selecct MAX (Age) From Persons) MaxAge, --The highest age of the table
p.Age ConcretPersonAge -- The age of the person X
FROM
Persons P

|||Tahnk you very much. But I think I've simplified too much my query problem. I've got a third column. That its' cities. The original table is actually "Age", "Person","City". I need the maximum age and the age of the person X in each city (multiple rows result table). Could you help me?

Thank you evryone, specially:Mani and Simon.|||

In which case a derived table is the best option

select maxAge.age

, person.age

, city

from (select max(age) age, city from person where name = @.name group by city ) maxage

join person on person.city = maxage.city

where name = @.name