MySQL select not equal to limit in subquery

Question

MySQL #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Given 1 table as following

Item | Name       | Price
----- ------------ --------
1    | Adidas     | 310.00
2    | Nike Run   |  30.00
3    | Puma       | 150.00
4    | Nike Women |  20.00
5    | NB         |  20.00

Would like to select records and return the sum amount. Do not sum up the 2 highest prices' record.

SELECT SUM(Price) as total_amount
FROM `test`
WHERE Item NOT IN (
    SELECT Price 
    FROM `test`
    ORDER BY Price DESC
    LIMIT 2)

Expected Result:

total_amount
------------
   70.00

How to use JOIN or alternative LIMIT in Subquery in this query?

Thank you.

I think your subquery has a typo.

Tim Biegeleisen
– Tim Biegeleisen

2016-08-05 03:33:03 +00:00
Commented Aug 5, 2016 at 3:33 — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 5, 2016 at 3:33

SIDU · Accepted Answer · 2016-08-05 03:39:23Z

1

You need a temp table:

SELECT SUM(Price) FROM test WHERE Item NOT IN (
    SELECT * FROM (
        SELECT Item FROM test ORDER BY Price DESC LIMIT 2
    ) AS tmp
)

answered Aug 5, 2016 at 3:39

SIDU

2,2781 gold badge16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sgeddes · Accepted Answer · 2016-08-05 03:52:54Z

1

Here's one option using a subquery with limit / offset:

select sum(price)
from (
  select *
  from test
  order by price desc
  limit 999999999
  offset 2
  ) t

SQL Fiddle Demo

Just make sure the limit value is greater than the number of potential rows (which evidently is 18446744073709551615)...

Or you could use user-defined variables:

select sum(price)
from (
  select *, @rn:=@rn + 1 rn
  from test cross join (select @rn:= 0) t
  ) t
where rn > 2

If you looking to exclude the 2 highest prices which could be more than 2 records, this will also work with user defined variables:

select sum(price)
from (
  select *, @rn:=if(@prevPrice=price, @rn,
                    if(@prevPrice:=price, @rn + 1, @rn + 1)) rn
  from test cross join (select @rn:= 0, @prevPrice:= null) t
  ) t
where rn > 2

More Fiddle

edited Aug 5, 2016 at 3:52

answered Aug 5, 2016 at 3:33

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

3 Comments

Vamsi Prabhala Over a year ago

@sgeddes..when there are ties for the maximum value..this query would include the second max value in the sum.

sgeddes Over a year ago

@vkp -- good point, perhaps needs to be better defined by the OP.

sgeddes Over a year ago

@vkp -- edited with an alternative solution for both options using user-defined variables...

Collectives™ on Stack Overflow

MySQL select not equal to limit in subquery

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related