Home > database >  Mysql query to select row based on column value
Mysql query to select row based on column value

Time:11-18

id date is_available product_id product_recalled
200 2019-10-10 1 123 yes
201 2020-07-10 1 123 no
202 2020-08-11 0 123 yes
203 2021-07-10 0 123 yes
204 2021-01-10 0 123 no
205 2021-07-10 0 124 yes
206 2021-01-10 0 124 no

I need a query to select the max row based on the is_available column value sorted by latest date. If is_available = 1 for the product, I should get the max row for with is_available = 0. Similarly, if a product only has is_available set to 0, I should get the max row for is_available = 0.

If a product id has both is_available =1 and is_available = 0, then I need to get the latest record with is_available = 1 which is the case for product id 123. For product id 124, there is no is_available = 1 so I need to get the latest record with is_available = 0.

Eg: Scenario 1: If I specify the product_id = 123, then the expected output should be

id date is_available product_id product_recalled
201 2020-07-10 1 123 no

Scenario 2: If I specify the product_id = 124, then the expected output should be

id date is_available product_id product_recalled
205 2021-07-10 0 124 yes

I am not sure how to write a single query that would do this. Any help would be appreciated.

CodePudding user response:

WITH cte AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY is_available DESC, date DESC) rn
  FROM test
)
SELECT *
FROM cte
WHERE rn = 1;

For to select the row for definite product use

SELECT *
FROM test
WHERE product_id = @product_id
ORDER BY is_available DESC, date DESC
LIMIT 1;

fiddle

  • Related