Mysql Query help for get which column not have maximum % value

[k_manimuthu]

SAMPLE TABLE 1
===================================
product         Division     sales %
===================================
product1    divsion1    99
product2    divsion1    51
product3    divsion1    50
product4    divsion2    98
product5    divsion2    41
product6    divsion2    40
product7    divsion3    97
product8    divsion3    31
product9    divsion3    30

===================================
Expected output 1
===================================
product2    divsion1    51
product3    divsion1    50
product5    divsion2    41
product6    divsion2    40
product8    divsion3    31
product9    divsion3    30

Hi All,
I am having a single table. In this table I need to get product name which is not having maximum sale % for each division.
Please help me to get this results. Thanks in advance.

[ryantroop]

what have you tried?

It looks like you can do it with some subqueries and a NOT IN, but that would likely be very inefficient.. perhaps joining against itself and doing some sort of group by?

[AndrisP]

In to the subquery you can select max(sales) group by Division then select * from SAMPLE where product not in (subquery) eg

select
    t.*
from
    `sample` t
where
    t.`product` not in (
        select
            s.`product` from (
            select
                 m.`product`
                ,max(m.`sales`)
            from
                `sample` m
            group by m.`Division`
        ) s
    )
order by
     t.`Division` asc
    ,t.`sales` desc;

if your MySQL version is 5.7 then before this query set sql_mode

set sql_mode=(select replace(lower(@@sql_mode),'only_full_group_by',''));

[k_manimuthu]

Hi AntrisP,
Thanks for you solution. But i didn't get results if maximum sales % having more than entries.

product1    division1   99
product2    division1   45
product3    division1   50
product4    division2   98
product5    division2   41
product6    division2   40
product7    division3   97
product8    division3   31
product9    division3   30
product10   division1   99
product11   division1   99

@ryantroop, I am new in mysql. I tried with INNER JOIN, GROUP BY and NOT in only. But i didn't get expected result.

[AndrisP]

You can define function

delimiter $$
create function not_max_of_division(
     p_product varchar(30)
    ,p_division varchar(30)
) returns boolean
begin
    declare v_max int;
    declare v_not_max boolean;
    select
        max(t.`sales`) into v_max
    from
        `sample` t
    where
        t.`Division` = p_division;
    select
        t.`sales` <> v_max into v_not_max
    from
        `sample` t
    where
        t.`product` = p_product;
    return v_not_max;
end $$
delimiter ;

and then

select t.* from `sample` t
where not_max_of_division(t.`product`, t.`Division`)
order by
     t.`Division` asc
    ,t.`sales` desc;

(do not need set sql_mode)

[k_manimuthu]

Hi AntrisP, Thanks for your solution.

A dirty one:

SELECT DISTINCT a.*
FROM `products` AS a                   
  LEFT JOIN `products` AS b            
      ON a.product_id = b.product_id AND a.sales < b.sales
WHERE b.sales IS NOT NULL