Oracle SQL : How to fetch a row that has the maximum value for a column

Last Updated on :April 5, 2020

You often need to fetch a row that has maximum value for a specific column. For example, we have an ORDERS table and we want to list out latest order details for each customer.

Order Table Output

Here you can see each customer placed one or more orders. If you want to fetch latest order details for each customer then you can query like this

SELECT ORDER_ID,
  customer_id,
  date_ordered,
  sales_persion_id,
  total
FROM
  (SELECT ORDER_ID,
    customer_id,
    date_ordered,
    sales_persion_id,
    total ,
    MAX(date_ordered) over (partition BY customer_id) max_date_ordered
  FROM ORDERS
  )
WHERE date_ordered = max_date_ordered;

The inner query will fetch all the records and use analytical function MAX (date_ordered) over (partition BY customer_id) to calculate maximum order date for each customer.
The outer query takes only the record where date ordered is equal to maximum order date.

Output

Here is similar query to fetch maximum total amount placed by each customer.

SELECT ORDER_ID,
  customer_id,
  date_ordered,
  sales_persion_id,
  total
FROM
  (SELECT ORDER_ID,
    customer_id,
    date_ordered,
    sales_persion_id,
    total ,
    MAX(total) over (partition BY customer_id) max_total
  FROM ORDERS
  )
WHERE total = max_total;

Output

You can also write query like below to list out latest order details for each customer without using analytical function.

SELECT order_id,
  customer_id,
  date_ordered,
  sales_persion_id,
  total
FROM ORDERS o1
WHERE date_ordered=
  (SELECT MAX(date_ordered) FROM ORDERS o2 WHERE o2.customer_id=o1.customer_id
  );