How to get specific rows from table using "group by" and "order by"

Question

I have a table as follows:

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 the first available product for each product_id. Available products must first check product_availability, then is_available, and finally stock_count. (The product is available when product_availability is 1, then is_available is 1 and there is at least one product 1# in stock ##.)

I want to show the available products first, if there are no available products it doesn't matter which product is shown (in case of unavailable the first product is fine too).

(In the example above, I need to first get the product with tmp_id as 2.)

question: My question is how do I write a MYSQL query to achieve my needs?

I can get my products in the order I want using the following command, but I don't know what to do next to get the first existing product using

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 demonstration of what I have, the actual code is more complex and has multiple joins and other stuff.

Answer 1

This can be done using row_number(), which returns the unique row number for each row within the 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;