CODEWITHSUNDEEP
mysql
Sriya
Sriya

Reputation: 187

How use MAX in mysql to get follow result

This is the query

SELECT research_id,
    IF(product_id = 4, IF(value REGEXP '^-?[0-9]+$' > 0, value, IF(value = 'Yes', 1, 0)), 0) AS val1,
    IF(product_id = 8, IF(value REGEXP '^-?[0-9]+$' > 0, value, IF(value = 'Yes', 1, 0)), 0) AS val2
FROM research_product_details rpd
LEFT JOIN products p
ON rpd.product_id = p.id
WHERE (product_id = 4 AND value >= 50) OR (product_id = 8 AND value >= 50)
ORDER BY research_id ASC , product_id ASC

And i got this result from this query

enter image description here

i want it like follow

enter image description here

Upvotes: 0

Views: 27

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

As @Jkike mentioned in his comment, one way to achieve what you want is to simply wrap your current query and GROUP BY the research_id column, selecting the max value for the val1 and val2 columns:

SELECT t.research_id AS research_id, MAX(t.val1) AS val1, MAX(t.val2) AS val2
FROM
(
    SELECT research_id,
        IF(product_id = 4, IF(value REGEXP '^-?[0-9]+$' > 0, value, IF(value = 'Yes', 1, 0)), 0) AS val1,
        IF(product_id = 8, IF(value REGEXP '^-?[0-9]+$' > 0, value, IF(value = 'Yes', 1, 0)), 0) AS val2
    FROM research_product_details rpd
    LEFT JOIN products p
    ON rpd.product_id = p.id
    WHERE (product_id = 4 AND value >= 50) OR (product_id = 8 AND value >= 50)
    ORDER BY research_id ASC , product_id ASC
) AS t
GROUP BY t.research_id

Upvotes: 1

Related Questions