How to get specific row from a table with "group by" and "order by"

I have a table like below:

tmp_id	product_availability (0 and 1)	is_available (0 and 1)	stock_count (integer)	product_id (integer)
1	1	1	0	1
2	1	1	4	1

I need to get first available product for each product_id. Available products MUST check product_availability first, then is_available and last check stock_count. (A product is available when product_availability is 1, then is_available is 1 and in in stock with at least one product 1.)

I want to show available products first, if there is no available product, it doesn't matter which product to show (first product is OK in unavailable situation).

(In above example I need to get product with tmp_id of 2 first.)

Question: My question is how to write the MYSQL query to achieve my needs?

I can get my products in the wanted order with below command, but I don't know what to do next to get first existing product with GROUP BY:

SELECT
    pa.*
FROM
    `product_advanced` AS `pa`
ORDER BY
    `pa`.`product_availability` DESC,
    `pa`.`is_available` DESC,
    `pa`.`stock_count` DESC

NOTE: Of course this is just a simple presentation of what I have, actual code is more complicated and have multiple joins and other things.

Solution

This can be done using row_number() to return Returns a unique row number for each row within a partition

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY product_availability DESC, is_available DESC, stock_count DESC) AS rn
  FROM product_advanced
)
SELECT tmp_id, product_availability, is_available, stock_count, product_id  
FROM cte 
WHERE rn = 1;