How to Get the Max Value in SQL Server

You can find the max value in SQL Server using:

SELECT 
max(field_name) as new_field_name 
FROM [database_name].[table_schema].[table_name]

Next, you’ll see how to find the max value across the following 3 scenarios:

1. Under a Single Field
2. After Joining Tables
3. Using Group By

(1) Find the Max Value Under a Single Field in SQL Server

To start with a simple example, let’s create a table called ‘PersonAge‘ (with a dbo schema):

You can then find the max age by running the following query (where the database name is TestDatabase):

SELECT 
max(Age) as Max_Age 
FROM [TestDatabase].[dbo].[PersonAge]

As you can see, the max age is 65:

(2) Find the Max Value After Joining Tables in SQL Server

Let’s add a second table called ‘People‘ (with a dbo schema):

The ‘People’ table can then be joined to the ‘PersonAge’ table using the Age_ID field (which can be found under both tables):

SELECT
pr.First_Name,
pr.Last_Name,
ag.Age
FROM [TestDatabase].[dbo].[People] pr
LEFT JOIN [TestDatabase].[dbo].[PersonAge] ag ON pr.Age_ID = ag.Age_ID

Here are the results of the joined tables:

Suppose that you want to find the max age after joining the tables together. In that case, you’ll need to add the following WHERE clause at the bottom of the query:

WHERE ag.Age = (SELECT max(Age) FROM [TestDatabase].[dbo].[PersonAge])

So the full query would look as follows:

SELECT
pr.First_Name,
pr.Last_Name,
ag.Age
FROM [TestDatabase].[dbo].[People] pr
LEFT JOIN [TestDatabase].[dbo].[PersonAge] ag ON pr.Age_ID = ag.Age_ID
WHERE ag.Age = (SELECT max(Age) FROM [TestDatabase].[dbo].[PersonAge])

You’ll then get the complete record where the max age is 65:

(3) Apply a Group By

Finally, the query below can be used to get the ‘City‘ and ‘Age‘ fields:

SELECT
pr.City,
ag.Age
FROM [TestDatabase].[dbo].[People] pr
LEFT JOIN [TestDatabase].[dbo].[PersonAge] ag ON pr.Age_ID = ag.Age_ID

Here is the result:

What if you’d like to find the max age per city?

In that case, you’ll need to add GROUP BY pr.City at the bottom of the query:

SELECT
pr.City,
max(ag.Age) as Max_Age 
FROM [TestDatabase].[dbo].[People] pr
LEFT JOIN [TestDatabase].[dbo].[PersonAge] ag ON pr.Age_ID = ag.Age_ID
GROUP BY pr.City

You’ll now get the max age per city:

If you were interested to find the max age for a particular city, you can specify the name of the city in the WHERE clause.

For example, let’s say that you want to find the max age for London. In that case, you’ll need to add the following WHERE clause:

WHERE pr.City = 'London' 

So the complete query to find the max age for London is:

SELECT
pr.City,
max(ag.Age) as Max_Age 
FROM [TestDatabase].[dbo].[People] pr
LEFT JOIN [TestDatabase].[dbo].[PersonAge] ag ON pr.Age_ID = ag.Age_ID
WHERE pr.City = 'London' 
GROUP BY pr.City

Therefore, the max age for London is 65:

Note that the WHERE clause needs to be placed before the GROUP BY.