CODEWITHSUNDEEP
sqloracle-databasemaxsql-limit
user3137110
user3137110

Reputation: 339

Getting the Highest value from a table

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

Upvotes: 0

Views: 1160

Answers (2)

user3137110
user3137110

Reputation: 339

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

Upvotes: 1

Ditto
Ditto

Reputation: 3344

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]

Upvotes: 1

Related Questions