List record from table by selects MAX value

Question

i have a table as following:

Account(Acc_number,Acc_balance,branch_code)

Now i want to list all account details in each branch with the highest balance, how should I write the sql statement?

Simply said, i just want to list out all the branches, with the account of having highest balance.

score 2 · Accepted Answer · edited May 23 '17 at 12:11

2

There are similar questions.

You can use analytic functions.

Try this if it works for you:

    select branch_code,acc_number,acc_balance from
(
        select distinct acc_number,branch_code,acc_balance, 
        max(acc_balance) over(partition by branch_code order by 1) as MAX 
        from Account
)

    where acc_balance=MAX

edited May 23 '17 at 12:11

Community

1
1

answered Dec 02 '11 at 00:48

bonsvr

2,262
5
22
33

thanks for the answer, but Your result listed all the accounts, what i want is only listed one highest account in every branch. – C PWL Dec 02 '11 at 01:36
I revised my answer accordingly. – bonsvr Dec 02 '11 at 01:52

List record from table by selects MAX value

1 Answers1