Link to home
Start Free TrialLog in
Avatar of Lia Nungaray
Lia NungarayFlag for United States of America

asked on

How to retrieve row with Maximum value in Oracle

I need to find the record with the maximum amount of sales, regardless of any other data. I then need to find the client info which is contained in a separate table. Lets say that the following row contains the maximum amount, which is 5,000. How do I retrieve the client_id 1001 so I can use this value and retrieve the client info? Bear in mind that this is a huge table, with millions of transactions, so I need to make it as efficient as possible.


transaction_id   amount      client_id

156897             5,000         1001


client_id            client_name      client_contact

1001                  ACME               Coyote Ugly


Thanks

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I'd first try something like:
with max_amt as (
select client_id, max(amount) from transactions group by client_id
)
select client_id, client_name, client_contact
from clients
join max_amt using(client_id)

Open in new window

Avatar of Lia Nungaray

ASKER

This query returns the maximum amount for each client. I just want the one client which has the maximum amount.

Before I try again:
What if two clients tie?
Assuming you want to return all in a tie:
with max_amt_rn as (
select client_id, row_number() over(order by amount desc from transactions) rn
from transactions
),
max_amt as (
select client_id from max_amt_rn where rn=1
)
select client_id, client_name, client_contact
from clients
join max_amt using(client_id)

Open in new window

Try this

select b.client_id, b.client_name, b.client_contact, a. Amount
from
(select client_id, amount from transactions
from transactions where rank() over(order by amount desc) =1) a
Join clients b where b.client_id=a.client_id

Open in new window

>>Try this

Yes, rank is what you would want over row_number.

Two things wrong with it otherwise.
First:  You have "from transactions" twice.
Second and the most important:  You cannot use window functions in the WHERE clause.  That is why I used a CTE to do it.

If you fix yours, you will then have pretty much exactly what I posted with the exception of rank instead of row_number.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>If you must have only 1 row then limit the output

Then the row_number I originally posted will do that.  The tie-breaker can be done in the order by.

Yes, that is true. We await knowledge on if only 1 row is needed or not, and what the tiebreaker logic could be.