Suppose i have a table t1:

What i want to know is, for a unique product id i want to see if there has been a new price.

Only having constant values for a specific product id is of not interest.

Would appreciate some tips.

Best regards

My thoughts is something like "Select the product ids' where its distinct(count(price)) > 1. This should give me only the products ids that has a price change?

If a product id only has the same price without a change, it's of no interest to select.

CodePudding user response：

Your idea to check COUNT(DISTINCT price > 1) is correct.

We just need to use GROUP BY with a HAVING clause:

SELECT product_id
FROM t1
GROUP BY product_id
HAVING COUNT (DISTINCT price) > 1
ORDER BY product_id;

If also the different prices should be shown, this query can be used as subquery. For example:

SELECT product_id, price
FROM t1
WHERE product_id IN 
(SELECT product_id
FROM t1
GROUP BY product_id
HAVING COUNT (DISTINCT price) > 1)
ORDER BY product_id;

Or we could use JOIN rather than IN which could have a better performance:

SELECT t1.product_id, t1.price
FROM t1
JOIN  
(SELECT product_id
FROM t1
GROUP BY product_id
HAVING COUNT (DISTINCT price) > 1) sub
ON t1.product_id = sub.product_id
ORDER BY t1.product_id;

We can try out these queries here: db<>fiddle