How to define own aggregate function in Mysql for GROUP BY?

Question

I have a products table with product names in different languages:

product-id | lang-no | name

I want to list each product once, but using a different language name.

I dont't have all languages for all product names, so I have to fall back onto another language sometimes.

To pick the language with the lowest or highest number I use

SELECT * FROM products JOIN
(SELECT product-id, MIN(lang-no) AS minlang FROM products GROUP BY product-id)
AS u ON products.product-id = u.product-id AND products.lang-no=minlang

But now I need to define another aggregate function instead of MIN or MAX so I can prefer lang-no 3 for example.

How do i define my own aggregate function in Mysql, eg. some IF logic ?

Answer 1

You can use case with aggregates to specify which value to return if present and if not min (or max) value:

select p.*
from products p
join (
    select product_id,
        case 
            when sum(lang_no = 3) > 0
                then 3
            else min(lang_no)
            end as min_lang_no
    from products
    group by product_id
    ) p2 on p.product_id = p2.product_id
    and p.lang_no = p2.min_lang_no