0

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.

MMDM
  • 465
  • 3
  • 11
  • [MySql top 1 row in each group](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) – Stu Jun 07 '23 at 10:55

1 Answers1

1

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;
SelVazi
  • 10,028
  • 2
  • 13
  • 29
  • Actually it works! But I have another question, is it any performance issue with it? because I have at least 5k rows and it grows too. – MMDM Jun 07 '23 at 11:26
  • Another thing, if I want to change my driver from mysql to another sql driver, is it work? or any global solution for all sql langs? – MMDM Jun 07 '23 at 11:28
  • It is working on most of databases, concerning the performance you probably need to add some indexes – SelVazi Jun 07 '23 at 13:36