sql query to fetch the third highest sale grouped by sales

Question

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`

You should start with the duplicate link, try a query, and then if you get stuck come back here for help. — Tim Biegeleisen, Apr 09 '18 at 11:41
Tag your question with the database you are using. MySQL is not consistent with the sample code. — Gordon Linoff, Apr 09 '18 at 11:57

Gordon Linoff · Answer 1 · 2018-04-09T16:42:39.430

1

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
                      );

edited Apr 09 '18 at 16:42

answered Apr 09 '18 at 11:58

Gordon Linoff

1,242,037
58
646
786

this is exactly what I was looking at – B L Praveen Apr 09 '18 at 13:05
Does this query run on MySQL server? – B L Praveen Apr 09 '18 at 13:11
@BLPraveen . . . It will if you use `limit` instead of `offset/fetch`. – Gordon Linoff Apr 09 '18 at 16:41
Is there another way without using limit – B L Praveen Apr 10 '18 at 09:19
@BLPraveen . . . Why are you asking? Doesn't this answer your question? – Gordon Linoff Apr 11 '18 at 03:14
In interview they asked to try without using limit – B L Praveen Apr 12 '18 at 09:57
@BLPraveen . . . You should ask another question. This answers the question you did ask. Changing the question can invalidate answers, which draw downvotes. – Gordon Linoff Apr 12 '18 at 12:18

score 0 · Answer 2 · answered Apr 09 '18 at 12:28

0

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

answered Apr 09 '18 at 12:28

Mohini

268
3
15

sql query to fetch the third highest sale grouped by sales

2 Answers2