Getting the Highest value from a table

Question

I am trying to display just the first row, in MYSQL the example was:

ORDER BY foo DESC LIMIT 1;

I believe oracle does not use the LIMIT clause. I have tried the ROWNUM = 1, but it does not seem to work. Is this the proper way of displaying the first line?

select customer_name, 
       MAX(balance) as "Highest Depositor Value"
  from depositor
 inner join account
    on depositor.account_number = account.account_number
 group by customer_name, balance
 order by balance
 where rownum = 1;


ERROR at line 4:
ORA-00933: SQL command not properly ended

I got the answer! Thanks

select customer_name,max(balance) as "Highest Depositor Value"
 from depositor
 inner join account
    on depositor.account_number = account.account_number
 group by customer_name, balance order by balance desc
 fetch first 1 rows only;


CUSTOMER_NAME   Highest Depositor Value
 --------------- -----------------------
 Lindsay                          100000

Ditto · Answer 1 · 2015-09-28T19:16:48.043

1

remove this part:

order by balance where rownum = 1

and what you have should work ..

MAX will give you the largest ... you don't need to tell it how many rows, MAX will only give the 1 largest value.

[edit] if you just want the 1 largest value, you need to remove the group/order by .. and also remove "customer name" from select:

  select MAX(balance) as "Highest Depositor Value"
  from depositor
  inner join account on depositor.account_number = account.account_number;

[/edit]

edited Sep 28 '15 at 19:16

answered Sep 28 '15 at 18:42

Ditto

3,122
1
11
28

I tried that and I end up with 11 rows selected. That is why I wanted to order them and display the first row. – user3137110 Sep 28 '15 at 18:52
Oh yeah sorry .. 11 rows, 1 row per group .. you grouped on customer name, balance .. perhaps you don't want to group ? you just the 1 max value? remove both GROUP by and ORDER by .. – Ditto Sep 28 '15 at 19:15
I will give a little background. the customer_name come from the depositor table. each customer_name can have several accounts. I used group to group them together and get a total value of there accounts. I can get a nice result like that. The problem is I just wanted the max value of the grouped customers. Maybe grouping was not the way to go for adding the values up, but it was the way I proceeded I can see the top value but i am not able display only that value. – user3137110 Sep 28 '15 at 19:26
Post this information in the original question .. include sample data, and expected results. – Ditto Sep 28 '15 at 19:35

score 1 · Answer 2 · edited May 23 '17 at 12:31

I found the answer explained well here.

How do I limit the number of rows returned by an Oracle query after ordering?

  select customer_name,max(balance) as "Highest Depositor Value"
2  from depositor
3  inner join account on depositor.account_number = account.account_number
4  group by customer_name, balance order by balance desc
5  fetch first 1 rows only;
CUSTOMER_NAME   Highest Depositor Value
 --------------- -----------------------
 Lindsay                          100000

Getting the Highest value from a table

2 Answers2