Question 55662 in Mysql Question posted in
The official MySQL documentation can be found here.

Get the row with minimum property value in group by using MySQL

I have a table (let’s say, prod_config) with the below data-

Prod_Id Config_Id Config_Type
9856 1 2
9855 93 1
9855 92 2
9855 91 2
9854 93 1
9854 92 2
9854 91 2
9849 93 1
9849 92 2
9850 91 2
9852 88 1
9852 90 2
9853 100 2

The relation is in such a way that —

  • Prod_Id can be mapped to a single or multiple Config_Ids (of Config_Type 1 or 2)

  • If ProdId have single occurence, then ConfigType will always be 2

  • If ProdId have multiple occurence, there will be only single row with ConfigType as 1

I need to find the below result having–

  • all ProdIds having single occurrence

  • For all ProdIds having multiple occurrences, select only the row with Config_Type as 1

Prod_Id Config_Id Config_Type
9856 1 2
9855 93 1
9854 93 1
9849 93 1
9850 91 2
9852 88 1
9853 100 2
Tags:

4

Answers


  1. 0

    You can use multiple window functions to get your required result –

    SELECT *
  FROM (SELECT Prod_Id, Config_Id, Config_Type
               COUNT(*) OVER(PARTITION BY Prod_Id) cnt_prod_id
          FROM your_table 
       )
 WHERE cnt_prod_id = 1
    OR (cnt_prod_id > 1 AND Config_Type = 1);
    Login or Signup to reply.
  2. 0

    FIDDLE

    SELECT t1.Prod_Id, t1.Config_Id, t1.Config_Type
    FROM prod_config t1
    LEFT JOIN (
      SELECT Prod_Id, MIN(Config_Id) AS Config_Id
      FROM prod_config
      WHERE Config_Type = 1 OR NOT EXISTS (
        SELECT * FROM prod_config t2
        WHERE t2.Prod_Id = prod_config.Prod_Id AND t2.Config_Type = 1
      )
      GROUP BY Prod_Id
    ) t2 ON t1.Prod_Id = t2.Prod_Id AND t1.Config_Id = t2.Config_Id
    WHERE t2.Prod_Id IS NOT NULL;

    The subquery
    t2
    selects the minimum
    Config_Id
    for each
    Prod_Id
    where
    Config_Type=1
    .

    If there are no rows with
    Config_Type=1
    for a given
    Prod_Id
    , then the subquery selects the row with the minimum
    Config_Id
    .

    The outer query joins the original table with the subquery on both
    Prod_Id
    and
    Config_Id
    .

    Prod_Id
    is null in the joined result, which means those are not part of the final result

    Login or Signup to reply.
  3. 0

    Here is one fairly easy to understand:

    select * 
from prod_config
where prod_id in (select prod_id from prod_config
                group by prod_id
                having count(*)=1)
    or 
        (prod_id not in (select prod_id from prod_config
                        group by prod_id
                        having count(*)=1)
            and config_type=1)
;

    But on further thought, as there can be only one config_type=1 existing for a given multiple-instance prod_id, we can simplify it to :

    select * 
from prod_config p
where prod_id in (select prod_id from prod_config
                group by prod_id
                having count(*)=1)
    or 
         config_type=1
;
    Login or Signup to reply.
  4. 0

    You could achieve your desired output using below query

    SELECT t1.Prod_Id, t1.Config_Id, t1.Config_Type
FROM prod_config t1
WHERE NOT EXISTS (
    SELECT 1 
    FROM prod_config t2
    WHERE t2.Prod_Id = t1.Prod_Id AND t2.Config_Id > t1.Config_Id);

    See demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search