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:
- Under a Single Field
- After Joining Tables
- 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.