FB

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

9 Comments / Last updated: September 26, 2022

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.

 

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!

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

9 thoughts on “How to Select Rows with Max Value for a Column in Oracle SQL”

  2. Mandi

    Hi,
    I used this as a guide to achieve the result I needed but could not get it to work trying to use a table alias for the source table.
    Is that a restriction on this type of Sub Query?
    I am hoping to do some joins and aliases would make it easier than the full table names.

    Reply
  5. Frank Hines

    Hi, I was working at this last night and this morning. I sifted through so many articles until I found yours.

    You helped me solve my select max date issue. Thank you so much!

    Reply
  6. FABIO P

    Good evening!

    Very good this topic. It helped me a lot with an issue here at work.
    Congratulations and keep up the great work!

    Reply
  8. Tina

    HOW we using two analytics functions and a subquery?
    What if we want to find the person_id who made the MAX transactions in one year.
    To COUNT all the transactions for one person end then give me back the person for MAX transactions in one year.

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Table of Contents

→ Table of Contents