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

Question

I have a table like this: tmp_id product availability (0 and 1) is_available (0 and 1) stock_count (integer) product_id (integer) 1110121141 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. (When product_availability is 1, the product is available

P粉055726146 · Answer

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;

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