CODEWITHSUNDEEP
mysqlsql
B L Praveen
B L Praveen

Reputation: 1990

sql query to fetch the third highest sale grouped by sales

Fetch the third highest sale amount from the table (group by sale amount) in MySQL 

 select Top 3 * from t1 group by sale_Amnt 

|Id|product_name|product_group    |sale_Amnt(INR)
------------------------------------------------
 1|  p1       |   Cosmetic       |4485
 2|  p2       |   Cosmetic       |8525
 3|  p3       |   Health         |12589
 4|  p4       |   Health         |8525
 5|  p5       |   Home Appliances|9858
 6|  p6       |   Home Appliances|12589

Expected output

|Id|product_name|product_group    |sale_Amnt(INR)
------------------------------------------------
 2|  p2       |   Cosmetic       |8525
 4|  p4       |   Health         |8525`

Upvotes: 0

Views: 540

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

The proposed duplicate is a misunderstanding of the question. This question appears to be looking for the third highest value overall, but taking duplicates into account.

You can get the third row using offset/fetch in SQL Server:

select t.*
from t
where t.sale_amount = (select t2.sale_amount
                       from t t2
                       group by t2.sale_amount
                       order by t2.sale_amount desc
                       offset 2 fetch first 1 row only
                      );

In MySQL, that would be:

select t.*
from t
where t.sale_amount = (select t2.sale_amount
                       from t t2
                       group by t2.sale_amount
                       order by t2.sale_amount desc
                       limit 1 offset 2
                      );

Upvotes: 1

Mohini
Mohini

Reputation: 268

SELECT * FROM `sale_amnt` ORDER BY `sale_Amnt` DESC LIMIT 2,1

Upvotes: 0

Related Questions