Get last value defined for tree data in Mysql

Question

Product Tier1 Tier2 Tier3   value
 A       ANZ    -     -       2
 A       ANZ    DE    -       3
 A       ANZ    DE    Outlet  9
 B       EMEA   -     -       4
 B       EMEA   IT    -       6

Each time I am interested in the last value defined for the product. For example, for product A I should get a value of 9 and for product B I should get a value of 6.

Is it possible that I get the result directly using an SQL query or load all the product lines and perform operations in the internal code to get the final value. Which is preferable. I am using MYsql.

  ANZ       2        
   |
   DE       3
   |
   OUTLET - 9 last defined level value is 9


 EMEA   4
  |
 IT     6
  |
  ?     No value defined so last defined level value is 6

can be

case 1

 Product Tier1 Tier2 Tier3   value
     A       ANZ    -     -       2
     A       ANZ    DE    -       3
     A       ANZ    DE    Outlet  9

Desired result = 9 

case2

     Product Tier1 Tier2 Tier3   value
         A       ANZ    DE    Outlet  9

   Desired result = 9

case3

    Product Tier1 Tier2 Tier3   value
         A       ANZ    -     -       2
         A       ANZ    DE    Outlet  9

   Desired result = 9

case4

 Product Tier1 Tier2 Tier3   value
     A       ANZ    -     -       2
     A       ANZ    DE    -       3

   Desired result = 3 

Question 5

 case4
   Product Tier1 Tier2 Tier3   value
     A       ANZ    -     -       2

 Desired result = 2

Answer 1

Here, the solution with the data provided is checked.

SQL:

SELECT
    product,
    tier1,
    tier2,
    tier3,
    value
FROM 
    tbl tbl2
WHERE
    ( tbl2.product, ((tier1 IS NOT NULL) + (tier2 IS NOT NULL ) + (tier3 IS NOT NULL)) ) IN
        (
            SELECT 
                product,
                MAX( (tier1 IS NOT NULL) + (tier2 IS NOT NULL ) + (tier3 IS NOT NULL) ) maxTierCnt
            FROM
                tbl
            WHERE
                tbl.product = tbl2.product
        );

Output:

mysql> SELECT * FROM tbl;
+---------+-------+-------+--------+-------+
| product | tier1 | tier2 | tier3  | value |
+---------+-------+-------+--------+-------+
| A       | ANZ   | NULL  | NULL   |     2 |
| A       | ANZ   | DE    | NULL   |     3 |
| A       | ANZ   | DE    | Outlet |     9 |
| B       | EMEA  | NULL  | NULL   |     4 |
| B       | EMEA  | IT    | NULL   |     6 |
+---------+-------+-------+--------+-------+
5 rows in set (0.00 sec)

mysql>
mysql> SELECT
    ->     product,
    ->     tier1,
    ->     tier2,
    ->     tier3,
    ->     value
    -> FROM
    ->     tbl tbl2
    -> WHERE
    ->     ( tbl2.product, ((tier1 IS NOT NULL) + (tier2 IS NOT NULL ) + (tier3 IS NOT NULL)) ) IN
    ->         (
    ->             SELECT
    ->                 product,
    ->                 MAX( (tier1 IS NOT NULL) + (tier2 IS NOT NULL ) + (tier3 IS NOT NULL) ) maxTierCnt
    ->             FROM
    ->                 tbl
    ->             WHERE
    ->                 tbl.product = tbl2.product
    ->         );
+---------+-------+-------+--------+-------+
| product | tier1 | tier2 | tier3  | value |
+---------+-------+-------+--------+-------+
| A       | ANZ   | DE    | Outlet |     9 |
| B       | EMEA  | IT    | NULL   |     6 |
+---------+-------+-------+--------+-------+
2 rows in set (0.00 sec)

Answer 2

I think the following should do what you want:

SELECT value
FROM product
WHERE product.Product = 'A'
ORDER BY Tier1 DESC, Tier2 DESC, Tier3 DESC
LIMIT 1

... , 'A', .

Answer 3

, , sub-,

select * from table where product=(select Product B) and product=(select product A)

  select * from table where product=('SELECT value FROM product WHERE product.Product = 'A' ORDER BY id_product  DESC LIMIT 1')  and product=('SELECT value FROM product WHERE product.Product = 'b' ORDER BY id_product DESC  LIMIT 1')

, ,

: edit id_product ( ya), .

Answer 4

:

SELECT Product, Tier1, Tier2, Tier3
FROM (
  SELECT Product, Tier1, Tier2, Tier3,
         @rn := IF(@p = Product, @rn + 1,
                   IF(@p := Product, 1, 1)) AS rn
  FROM mytable
  CROSS JOIN (SELECT @rn := 0, @p = '') AS vars
  ORDER BY 
     Product, 
     IF(Tier3 = '-', 1, 0),
     IF(Tier2 = '-', 1, 0),
     IF(Tier1 = '-', 1, 0)) AS t
WHERE t.rn = 1     

ORDER BY , , .

, . @rn - 1 Product. @rn reset 1, .

Note. You can change the conditions Tierx = '-'to Tierx IS NULLif NULLused to indicate an empty level instead '-'.

Demo here

Answer 5

Try

select y.Product, y.value
from (
    select 
        Product, 
        max(case 
            when Tier3  is not null then 3 
            when Tier2  is not null then 2 
            when Tier1  is not null then 1
        else 0  end) max_Tier
    from tbl
    group by Product
) x
join (
    select 
        Product, 
        case 
            when Tier3  is not null then 3 
            when Tier2  is not null then 2 
            when Tier1  is not null then 1
        else 0  end Tier,
        value
    from tbl
) y
on x.Product = y.Product
and y.Tier = x.max_Tier