CODEWITHSUNDEEP
sql
Amber O
Amber O

Reputation: 67

Finding 2nd highest <attribute>

I have a table bills(name char(30),amount int, account_id int). I am trying to find out the 2nd highest amount from the table. Sample data is as below:

Name              | Amount  | Account_ID
Phoness Company   | 125     | 1
O Company         | 127     | 2
Phoness Company   | 187     | 1
sdfkjlef          | 45      | 3

My code below returns 125. It should return 127. Not sure what am I doing wrong here.

select min(amount) from (select top 2 amount from bills) b

Upvotes: 1

Views: 35

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

You have almost done:

select min(amount) from (select top 2 amount from bills order by amount desc) b

In case, if in your table there is more than one rows with highest value you can use this:

select min(amount) from (select distinct top 2 amount from bills order by amount desc) b

Upvotes: 2

Related Questions