How to Select Rows with Max Value for a Column in Oracle SQL

A common question in Oracle SQL, and SQL in general, is to select rows that match a maximum value for a column. Let's see how you can do that in SQL.

The Problem

Let's say you have a set of data that has some values in it.

These examples use a student enrolment database that I created:

1SELECT person_id,
2first_name,
3last_name,
4address_state,
5enrolment_date
6FROM person;

As you can see, it lists some information about people.

What if you needed to find the oldest person in each state? Or the youngest person in each state? Or the male and female that enrolled first?

This involves using analytics functions and a subquery.

You can use the steps in this article for any query where you need to select rows with MAX value for a column in Oracle SQL.

While you're here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

Step 1 - Find Max Value for Groups

We can skip all the way to the end to get the query that you need. But, I thought I should explain how you get there, because it can help you in the future when you write other queries.

For this example, let's say we want to find the latest enrolment in each state.

How do we get this?

Well, the person table has an enrolment_date column, and an address_state column. Let's use those.

1SELECT person_id,
2first_name,
3last_name,
4address_state,
5enrolment_date,
6MAX(enrolment_date) OVER (PARTITION BY address_state) AS last_enrolment_date
7FROM person;

This will give us a list of person records, and the latest enrolment date for each of them.

There are a few things to point out here.

There are two enrolment_date columns here. The first one, enrolment_date, is the actual value for that record.

The second date, the last_enrolment_date, is the latest enrolment date for the state that each person lives in.

How did we get this?

We used the OVER clause of the MAX function.

It was this line here:

1MAX(enrolment_date) OVER (PARTITION BY address_state) AS last_enrolment_date

The MAX function normally finds the max value in the entire table, but when we use the OVER clause and the PARTITION BY, we can group our MAX function. It's like saying, "get me the MAX value, but partition it by these values".

So, we've asked for the MAX enrolment_date, but for each occurrence of the address_state.

You'll see some duplicated values here, which is OK.

Now, we want to only show the records that are the maximum enrolment date.

Step 2 - Select Your Columns

Now, let's go back to the main table and work out what columns to display.

We'll come back to the MAX query later.

In the person table, let's say you wanted to see a few columns:

• Person ID
• First name
• Last name
• State
• Enrolment Date

So, let's write a query to select them.

1SELECT person_id,
2first_name,
3last_name,
4address_state,
5enrolment_date
6FROM person;

This will show us the main table, without any filtering.

Now, we want to SELECT from the results from the first step. We do this by using a subquery.

 1SELECT person_id,
 2first_name,
 3last_name,
 4address_state,
 5enrolment_date
 6FROM (
 7  SELECT person_id,
 8  first_name,
 9  last_name,
10  address_state,
11  enrolment_date,
12  MAX(enrolment_date) OVER (PARTITION BY address_state) AS last_enrolment_date
13  FROM person
14)
15WHERE enrolment_date = last_enrolment_date;

Notice that I've got two SELECT statements. The inner statement, inside the subquery, is the query from earlier. It shows the person information as well as the max enrolment_date for each person's state.

Then, I'm selecting a few columns to be displayed.

Finally, I'm limiting the result to only show records where the enrolment date is the same as the last_enrolment_date.

It's in a subquery to make the query easier to write, modify, and see the results of.

You can see in the table that there is only one record for each state, and it shows the person record for it.

How to Select Rows with MAX Value for a Column in Oracle SQL

So, in summary, to select rows with the MAX value for a column in Oracle SQL, you'll need a query like this:

1SELECT [columns to display]
2FROM (
3  SELECT [columns to display],
4  MAX([column_to_find_max_of])
5    OVER (PARTITION BY [column_to_group_the_max_by]) AS [new_column_name]
6  FROM [table]
7)
8WHERE [column_to_find_max_of] = [new_column_name];

Just substitute your values into this query and run it.

I hope this helps you solve your Oracle SQL problem to find the maximum values and the rows that match it!

While you're here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: