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):

How to Get the Max Value in SQL Server

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:

Maximum value

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

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

How to Get the Max Value in SQL Server

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:

Get the Max Value in SQL Server

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:

Record of max value

(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:

Maximum values

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:

How to Get the Maximum Value in SQL Server

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:

Get the Max Value

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