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:
Thanks Ben for all your hard work!
I usually find your posts well thought out and useful.
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.
would this be the same code for SQL server?
I think it should work, but I haven’t tested it.
Brilliant!!
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!
Good evening!
Very good this topic. It helped me a lot with an issue here at work.
Congratulations and keep up the great work!
thanks Ben, helped me with some work.
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.
Excellent article! Helped me resolve a complex query issue at my job. Thank you!
I have used these techniques often to find the latest or earliest characteristics of something. I am curious how you would approach a similar issue to return a characteristic based on an effective date that is not the first/last/etc.?
Example: I need to assign the account’s primary or secondary status for all monthly bills. Accounts default to ‘primary’ status and only get a row in t_acct_stat is the status changes.
t_bills
acct_id, bill_id, bill_dt, amt
A1234, B5678, 05-JAN-2023, 156.56
A1234, B5861, 04-FEB-2023, 163.24
A1234, B6257, 06-MAR-2023, 132.89
A1234, B6498, 05-APR-2023, 152.85
A1234, B6725, 05-MAY-2023, 169.34
A1234, B6953, 04-JUN-2023, 199.54
A4567, B5689, 05-JAN-2023, 254.23
A4567, B5872, 04-FEB-2023, 264.82
A4567, B6284, 06-MAR-2023, 210.52
A4567, B6512, 05-APR-2023, 264.16
A4567, B6752, 05-MAY-2023, 271.43
A4567, B6995, 04-JUN-2023, 302.18
A9876, B6764, 05-MAY-2023, 12.81
A9876, B6764, 05-JUN-2023, 38.52
t_acct_stat
acct_id, status, eff_dt
A1234, secondary, 28-FEB-2020
A1234, primary, 31-MAR-2023
A4567, secondary, 01-MAY-2023
Desired result
acct_id, bill_id, bill_dt, amt, status
A1234, B5678, 05-JAN-2023, 156.56, secondary
A1234, B5861, 04-FEB-2023, 163.24, secondary
A1234, B6257, 06-MAR-2023, 132.89, secondary
A1234, B6498, 05-APR-2023, 152.85, primary
A1234, B6725, 05-MAY-2023, 169.34, primary
A1234, B6953, 04-JUN-2023, 199.54, primary
A4567, B5689, 05-JAN-2023, 254.23, primary
A4567, B5872, 04-FEB-2023, 264.82, primary
A4567, B6284, 06-MAR-2023, 210.52, primary
A4567, B6512, 05-APR-2023, 264.16, secondary
A4567, B6752, 05-MAY-2023, 271.43, secondary
A4567, B6995, 04-JUN-2023, 302.18, secondary
A9876, B6764, 05-MAY-2023, 12.81, primary
A9876, B6764, 05-JUN-2023, 38.52, primary
Great explanation – simple example, fully explained. Thanks Ben