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:

SELECT person_id,
first_name,
last_name,
address_state,
enrolment_date
FROM person;

 

PERSON_ ID	FIRST_ NAME	LAST_ NAME	ADDRESS_ STATE	ENROLMENT_ DATE
1	Betty	Gonzales	Florida	01/Aug/2015 06:06:00
2	Nicole	Jordan	West Virginia	23/Sep/2015 05:04:00
3	Mary	Murphy	California	16/Nov/2015 22:34:00
4	Arthur	Moore	Florida	15/Apr/2015 15:26:00
5	Ernest	Bishop	New Jersey	04/Apr/2015 15:22:00
6	Teresa	White	Michigan	(NULL)
7	Ruby	Black	Ohio	02/Jul/2015 06:34:00
8	Sarah	Crawford	California	15/Mar/2015 14:07:00
9	Jeremy	Simmons	Wisconsin	18/Aug/2015 06:10:00
10	Rachel	Kelly	Georgia	21/Jan/2015 03:54:00
11	Phillip	Hunt	Arizona	16/May/2015 03:57:00
12	Robert	Daniels	Texas	25/Jun/2015 11:30:00
13	Nancy	Torres	Alabama	04/Dec/2015 08:20:00
14	Donna	Sims	Tennessee	04/May/2015 07:16:00
15	Brian	Stevens	Nevada	26/Dec/2015 02:48:00
16	Alan	Freeman	Illinois	21/Jan/2015 23:40:00
17	Janice	Castillo	Georgia	14/Aug/2015 13:36:00
18	Timothy	Rose	District of Columbia	20/Oct/2015 14:11:00
19	Gregory	Scott	California	03/Mar/2015 02:23:00
20	Alice	Ferguson	California	23/May/2015 05:24:00

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.

SELECT person_id,
first_name,
last_name,
address_state,
enrolment_date,
MAX(enrolment_date) OVER (PARTITION BY address_state) AS last_enrolment_date
FROM person;

PERSON_ID	FIRST_NAME	LAST_NAME	ADDRESS_STATE	ENROLMENT_DATE	LAST_ENROLMENT_DATE
13	Nancy	Torres	Alabama	04/DEC/15 08:20:00 AM	04/DEC/15 08:20:00 AM
11	Phillip	Hunt	Arizona	16/MAY/15 03:57:00 AM	16/MAY/15 03:57:00 AM
8	Sarah	Crawford	California	15/MAR/15 02:07:00 PM	16/NOV/15 10:34:00 PM
19	Gregory	Scott	California	03/MAR/15 02:23:00 AM	16/NOV/15 10:34:00 PM
3	Mary	Murphy	California	16/NOV/15 10:34:00 PM	16/NOV/15 10:34:00 PM
20	Alice	Ferguson	California	23/MAY/15 05:24:00 AM	16/NOV/15 10:34:00 PM
18	Timothy	Rose	District of Columbia	20/OCT/15 02:11:00 PM	20/OCT/15 02:11:00 PM
1	Betty	Gonzales	Florida	01/AUG/15 06:06:00 AM	01/AUG/15 06:06:00 AM
4	Arthur	Moore	Florida	15/APR/15 03:26:00 PM	01/AUG/15 06:06:00 AM
10	Rachel	Kelly	Georgia	21/JAN/15 03:54:00 AM	14/AUG/15 01:36:00 PM
17	Janice	Castillo	Georgia	14/AUG/15 01:36:00 PM	14/AUG/15 01:36:00 PM
16	Alan	Freeman	Illinois	21/JAN/15 11:40:00 PM	21/JAN/15 11:40:00 PM
6	Teresa	White	Michigan	(NULL)	(NULL)
15	Brian	Stevens	Nevada	26/DEC/15 02:48:00 AM	26/DEC/15 02:48:00 AM
5	Ernest	Bishop	New Jersey	04/APR/15 03:22:00 PM	04/APR/15 03:22:00 PM
7	Ruby	Black	Ohio	02/JUL/15 06:34:00 AM	02/JUL/15 06:34:00 AM
14	Donna	Sims	Tennessee	04/MAY/15 07:16:00 AM	04/MAY/15 07:16:00 AM
12	Robert	Daniels	Texas	25/JUN/15 11:30:00 AM	25/JUN/15 11:30:00 AM
2	Nicole	Jordan	West Virginia	23/SEP/15 05:04:00 AM	23/SEP/15 05:04:00 AM
9	Jeremy	Simmons	Wisconsin	18/AUG/15 06:10:00 AM	18/AUG/15 06:10:00 AM

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:

MAX(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.

SELECT person_id,
first_name,
last_name,
address_state,
enrolment_date
FROM person;

PERSON_ID	FIRST_NAME	LAST_NAME	ADDRESS_STATE	ENROLMENT_DATE
1	Betty	Gonzales	Florida	01/AUG/15 06:06:00 AM
2	Nicole	Jordan	West Virginia	23/SEP/15 05:04:00 AM
3	Mary	Murphy	California	16/NOV/15 10:34:00 PM
4	Arthur	Moore	Florida	15/APR/15 03:26:00 PM
5	Ernest	Bishop	New Jersey	04/APR/15 03:22:00 PM
6	Teresa	White	Michigan
7	Ruby	Black	Ohio	02/JUL/15 06:34:00 AM
8	Sarah	Crawford	California	15/MAR/15 02:07:00 PM
9	Jeremy	Simmons	Wisconsin	18/AUG/15 06:10:00 AM
10	Rachel	Kelly	Georgia	21/JAN/15 03:54:00 AM
11	Phillip	Hunt	Arizona	16/MAY/15 03:57:00 AM
12	Robert	Daniels	Texas	25/JUN/15 11:30:00 AM
13	Nancy	Torres	Alabama	04/DEC/15 08:20:00 AM
14	Donna	Sims	Tennessee	04/MAY/15 07:16:00 AM
15	Brian	Stevens	Nevada	26/DEC/15 02:48:00 AM
16	Alan	Freeman	Illinois	21/JAN/15 11:40:00 PM
17	Janice	Castillo	Georgia	14/AUG/15 01:36:00 PM
18	Timothy	Rose	District of Columbia	20/OCT/15 02:11:00 PM
19	Gregory	Scott	California	03/MAR/15 02:23:00 AM
20	Alice	Ferguson	California	23/MAY/15 05:24:00 AM

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.

SELECT person_id,
first_name,
last_name,
address_state,
enrolment_date
FROM (
  SELECT person_id,
  first_name,
  last_name,
  address_state,
  enrolment_date,
  MAX(enrolment_date) OVER (PARTITION BY address_state) AS last_enrolment_date
  FROM person
)
WHERE enrolment_date = last_enrolment_date;

PERSON_ID	FIRST_NAME	LAST_NAME	ADDRESS_STATE	ENROLMENT_DATE
13	Nancy	Torres	Alabama	04/DEC/15 08:20:00 AM
11	Phillip	Hunt	Arizona	16/MAY/15 03:57:00 AM
3	Mary	Murphy	California	16/NOV/15 10:34:00 PM
18	Timothy	Rose	District of Columbia	20/OCT/15 02:11:00 PM
1	Betty	Gonzales	Florida	01/AUG/15 06:06:00 AM
17	Janice	Castillo	Georgia	14/AUG/15 01:36:00 PM
16	Alan	Freeman	Illinois	21/JAN/15 11:40:00 PM
15	Brian	Stevens	Nevada	26/DEC/15 02:48:00 AM
5	Ernest	Bishop	New Jersey	04/APR/15 03:22:00 PM
7	Ruby	Black	Ohio	02/JUL/15 06:34:00 AM
14	Donna	Sims	Tennessee	04/MAY/15 07:16:00 AM
12	Robert	Daniels	Texas	25/JUN/15 11:30:00 AM
2	Nicole	Jordan	West Virginia	23/SEP/15 05:04:00 AM
9	Jeremy	Simmons	Wisconsin	18/AUG/15 06:10:00 AM

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:

SELECT [columns to display]
FROM (
  SELECT [columns to display],
  MAX([column_to_find_max_of])
    OVER (PARTITION BY [column_to_group_the_max_by]) AS [new_column_name]
  FROM [table]
)
WHERE [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: