How to fetch the rows with the Max Value for a column in PL/SQL?

PL/SQL provides useful tools for modifying and changing data. One common and crucial task is fetching rows with the maximum value for a specific column. Fetching rows is important for various applications, from creating reports to extracting data for analysis. In this blog, we will explore different methods of fetching rows with maximum values for a column in PL/SQL.

Table of Contents:

• Why Do We Need to Fetch Maximum Values in PL/SQL?
• Advantages of Fetching Maximum Values
• Method To Fetch Maximum Values for a Column in PL/SQL
  • Method 1: Using Subquery in PL/SQL
  • Method 2: Using Analytical Functions in PL/SQL
  • Method 3: Using JOINS with Subquery in PL/SQL
• Alternative Methods To Fetch Maximum Values for a Column
  • Method 1: Using GROUP BY in MySQL
  • Method 2: Using the DISTINCT ON function in PostgreSQL
• Performance Comparison
• Real-world Examples
• Best Practices
• Conclusion

Why Do We Need to Fetch Maximum Values in PL/SQL?

Retrieving maximum values is a common request in many business environments. For example, it helps businesses to determine the highest sales for the day, month, or year and determine the highest salary or most recently promoted employee of the company.

Advantages of Fetching Maximum Values

• Performance Improvement: Performance is improved by getting only the important data and avoiding irrelevant rows, which leads to faster query execution.
• Data Integrity: Maintaining report consistency by fetching accurate values from large datasets.
• Business Insights: Provides important data for businesses by identifying important details that can be used to create reports.
• Time Efficiency: Increases query speed and time taken to process the query, which improves decision-making.
• Improves Reporting: Helps in generating accurate reports and identifying future changes.

Master SQL Techniques – Kickstart Your SQL Learning Journey!

Enroll now and transform your future!

Explore Program

Method To Fetch Maximum Values for a Column in PL/SQL

Before getting started with the methods to fetch maximum values, let’s first create a Sales table and insert data into the table.

Example:

-Creating table
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    sales_amount DECIMAL(10, 2),
    region_id INT
);
---Inserting values
INSERT INTO sales (sale_id, product_name, sales_amount, region_id)
VALUES
(1, 'Product A', 5000, 1),
(2, 'Product B', 6000, 2),
(3, 'Product C', 7000, 1),
(4, 'Product D', 6500, 2),
(5, 'Product E', 8000, 1);
---Displaying result
select * from sales;

Output:

This is how the table looks after creating and inserting values in PL/SQL.

Let’s  create one more table called regions.

  --Create a table
CREATE TABLE regions (
    region_id INT PRIMARY KEY,
    region_name VARCHAR(100)
);
-- Insert some sample regions
INSERT INTO regions (region_id, region_name) 
VALUES
(1, 'North'),
(2, 'South');

Output:

This is how tables look after creating and inserting data.

Method 1: Using Subquery in PL/SQL

A subquery is a query within another query and is used in the WHERE, HAVING, or FROM clauses of a statement. Subqueries are mostly used with SELECT, UPDATE, INSERT, and DELETE statements for complex filtering and data modification.

Example:

SELECT * 
FROM sales 
WHERE sales_amount = (SELECT MAX(sales_amount) FROM sales);

Output:

Explanation: Here, this subquery (SELECT MAX(sales_amount) FROM sales) finds the maximum amount from the sales table.

Method 2: Using Analytical Functions in PL/SQL

Analytical functions of PL/SQL allow you to do calculations on a group of rows and retrieve maximum values by selecting only the first row in each group to eliminate duplicates.

1. ROW_NUMBER()

In PL/SQL, the ROW_NUMBER() window function is used to find a column’s largest value, assign sequential numbers to each row within a group, and delete duplicates by choosing only the first row in each group.

Example:

SELECT * 
FROM (
  SELECT sale_id, product_name, sales_amount, ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS row_num
  FROM sales
)
WHERE row_num = 1;

Output:

Explanation: Here, in this query, the ROW_NUMBER() function is used to select the highest sales amount.

2. RANK()

The RANK() function in PL/SQL is used to assign a unique rank to each row within a result set and leave gaps in the ranking if there are ties.

Example:

SELECT * 
FROM (
  SELECT sale_id, product_name, sales_amount, RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
  FROM sales
)
WHERE sales_rank = 1;

Output:

Explanation: Here, this query uses the  RANK() function to assign ranks to each row based on sales_amount in descending order.

Method 3: Using JOINS with Subquery in PL/SQL

Join queries that include sub-queries can be utilized to obtain data from multiple tables and are useful for performing complex queries, such as obtaining the maximum value for each group. It is useful in retrieving the main data together with additional information from joined related tables.

Example:

SELECT s.sale_id, s.product_name, s.sales_amount, s.region_id, r.region_name
FROM sales s
JOIN regions r
  ON s.region_id = r.region_id
WHERE s.sales_amount = (
    SELECT MAX(sales_amount) 
    FROM sales 
    WHERE region_id = s.region_id

);

Output:

Explanation: Here, this query selects the sale_id,product_name,sales_amount,region_id, and region_name for the sales with the maximum amount within each region.

Alternative Methods To Fetch Maximum Values for a Column 

PL/SQL provides analytical functions and subqueries, and other databases like MySQL and PostgreSQL have methods like GROUP BY and DISTINCT ON.

Method 1: Using GROUP BY in MySQL

We can use the GROUP BY clause to group data by certain columns, and we use the HAVING clause to filter the results for the maximum sales amount.

Example:

SELECT region_id, product_name, MAX(sales_amount) AS max_sales
FROM sales
GROUP BY region_id, product_name
HAVING sales_amount = (SELECT MAX(sales_amount) 
FROM sales 
 WHERE region_id = sales.region_id);

Output:

Explanation: Here, in this query, region_id and product_name group the data by region and product with the help of GROUP BY, and the HAVING clause makes sure that we only retrieve products with maximum sales.

Method 2: Using the DISTINCT ON function in PostgreSQL

DISTINCT ON is a feature in PostgreSQL that is used to return unique values by deleting duplicate values based on specific conditions.

Example:

SELECT DISTINCT ON (region_id) region_id, product_name, sales_amount
FROM sales
ORDER BY region_id, sales_amount DESC;

Output:

Explanation: Here, DISTINCT ON (region_id) selects the first row for each unique region_id, and ORDER BY region_id and sales_amount are in descending order.

Performance Comparison

Real-world Examples

1. Fetching the Latest Order: For a retail application, an employee wants to find the latest order made by each customer.

Example:

--Creating the table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    order_amount DECIMAL(10, 2)
);
  --Inserting data
INSERT INTO orders (order_id, customer_id, order_date, order_amount)
VALUES 
(1, 101, '2025-03-25', 180.30),
(2, 102, '2025-03-26', 250.00),
(3, 101, '2025-03-28', 200.00);
SELECT order_id, customer_id, order_date, order_amount
FROM orders
WHERE customer_id = 101
ORDER BY order_date DESC
LIMIT 1;
Output:

Explanation: Here, this Query(ORDER BY order_date DESC)  selects the most recent order and limits the result to only one row with LIMIT 1.

2. Fetching the latest transaction in the Bank Database: In a banking system, fetching the latest transaction for a customer.

Example:

CREATE TABLE transactions (
 transaction_id INT PRIMARY KEY,
customer_id INT,
transaction_date DATE,
 transaction_amount DECIMAL(10, 2)
);
INSERT INTO transactions (transaction_id, customer_id, transaction_date, transaction_amount)
VALUE
(1, 101, '2025-01-05', 500.00),
(2, 102, '2025-02-15', 1000.00),
(3, 101, '2025-03-01', 1500.50),
(4, 103, '2025-03-10', 1200.75),
(5, 101, '2025-03-15', 250.00),
(6, 102, '2025-03-20', 1200.00);
SELECT transaction_id, customer_id, transaction_date, transaction_amount
FROM (
SELECT transaction_id, customer_id, transaction_date, transaction_amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY transaction_date DESC) AS row_num
FROM transactions
) AS subquery
WHERE row_num = 1 AND customer_id = 101;

Output:

Explanation: Here, in this query, the ROW_NUMBER() function assigns a unique row number to each transaction, which is ordered by transaction_date in descending order.

Best Practices

• Window Functions for Large Datasets: Functions such as ROW_NUMBER() and RANK() can be used on tables with large datasets.
• Avoid Complex Joins in Subqueries: It is better to avoid nested subqueries for better performance when you have a large dataset.
• Indexing: Proper indexing on columns used in ORDER BY, GROUP BY, and WHERE clauses will improve performance.

Start Your SQL Adventure!

Start Your SQL Journey for Free Today

Explore Program

Conclusion

Fetching maximum values is a fundamental operation in PL/SQL and it also offers functions like ROW_NUMBER() and RANK() for better performance on large datasets, while MySQL and PostgreSQL provide useful methods like GROUP BY, HAVING, and DISTINCT ON, It is very important to choose the right functions according to data and performance needs.

Take your skills to the next level by enrolling in our SQL Training Course today and gain hands-on experience. Also, prepare for job interviews with our SQL interview questions, which are prepared by industry experts.

How to Fetch the Rows Which Have the Max Value for a Column in PL/SQL? – FAQs