Select Rows With Max Value

August 5, 2010

in Analytics, FAQ, SQL

Select Rows With Max Value

In Oracle SQL, we often need to select rows with max value of a specific column, such as date. Let’s say we have a table that stores customers’ orders, and we want to list the last order dates and amounts for each customer.

Here is a demo data set and query to achieve this. The same query can be extended to select rows based on the maximum value of any other column.

The table data set is:

SQL> select * from t_orders
  2  order by cust_id, order_date desc;

CUST_ID         ORDER_DAT  ORDER_AMT
--------------- --------- ----------
100             27-APR-10          8
100             17-JAN-10         21
101             02-AUG-10          7
101             26-JUL-10          4
101             17-APR-10          4
101             28-MAR-10         55
102             28-MAR-10          9
103             27-APR-10          4
103             18-MAR-10          3
103             17-JAN-10         15
103             07-JAN-10          1
104             26-JUN-10          2
104             18-MAR-10          2

13 rows selected.

We see from the table t_orders that four customers have placed a total of 13 orders. Of these we want to choose the latest order details for each customer.

The query to do this is:

SQL> -- Last order date of each customer
SQL> select cust_id
  2       , last_order_date
  3       , order_amt last_order_amt
  4  from
  5  (
  6    select cust_id
  7       , order_date
  8       , max(order_date) over
  9           (partition by cust_id) last_order_date
 10       , order_amt
 11    from t_orders
 12  )
 13  where order_date = last_order_date;

CUST_ID         LAST_ORDE LAST_ORDER_AMT
--------------- --------- --------------
100             27-APR-10              8
101             02-AUG-10              7
102             28-MAR-10              9
103             27-APR-10              4
104             26-JUN-10              2

The inner query uses the analytics function max() on a partition by customer id. The outer query picks that record for each customer in which the order date matches the maximum order date.

A similar query to select order details for the maximum order amount placed by each customer. In this case, max() is applied to order_amt.

SQL> -- Max order amount of each customer
SQL> select cust_id
  2       , order_date date_of_max_order
  3       , max_order_amt
  4  from
  5  (
  6    select cust_id
  7       , order_date
  8       , order_amt
  9       , max(order_amt) over
 10           (partition by cust_id) max_order_amt
 11    from t_orders
 12  )
 13  where order_amt = max_order_amt;

CUST_ID         DATE_OF_M MAX_ORDER_AMT
--------------- --------- -------------
100             17-JAN-10            21
101             28-MAR-10            55
102             28-MAR-10             9
103             17-JAN-10            15
104             26-JUN-10             2
104             18-MAR-10             2

Notice that in the second query, two records are retrieved for cust_id 104 as it has two orders with the same maximum amount.

Notes

See SQL to Select Rows Conditionally for other similar selection scenarios.

{ 3 comments… read them below or add one }

1 Rakesh Anand January 16, 2014 at 3:41 pm

This can be easily done by using group by. So, why did you write such long query.

Select cust_id, MAX(order_date)
From t_orders
Group By Cust_Id

2 rabba alam April 6, 2014 at 10:28 am

@Rakesh, the below query mentioned by you can pull only 2 attributes ( cust_id , max_order_date)
“Select cust_id, MAX(order_date) max_order_date
From t_orders
Group By Cust_Id”
If the requirement is to pull more attributes ,in addition to above two , you have to take help of either of the below two queries.

(i). analytics function max() on a partition by customer id as described in the above oratable post.
(ii). have to resort to the inline view as indicated in the query below :
select t.cust_id,b.last_order_date ,t.order_amt
from t_orders t , (select max(order_date) last_order_date
, cust_id from t_orders group by cust_id) b
where t.order_date = b.last_order_date
and t.cust_id = b.cust_id;

Of the two approaches , I find the first one better as it does not involve too many extra joins to restrict the final outcome.
Thanks to oratable for this well written post. 🙂

3 Koushik Paul July 25, 2018 at 4:59 pm

@ Rakesh, you can not select ID as it is not in group by or having clause.

If you want to understand why, try firing the query and check if u get the correct result.

Leave a Comment

Previous post:

Next post: