Sql-server – Query to fine “Order_num” that has no two equal “product_name”

querysql-server-2016subqueryt-sql

I have a table with below structure:

create table A_TEST
(
  order_num      int,
  product_name   varchar(50),
  product_amount int,
  product_price  int
);

Sample data :

insert into a_test (order_num , product_name , product_amount , product_price)
values (1,'APPLE',1000,2000),
       (1,'APPLE',2000,5000),
       (1,'APPLE',4000,3000),
       (2,'ORANGE',280,3000),
       (2,'APPLE',200,3000),
       (3,'BANANA',2000,3000),
       (3,'APPLE',200,3000),
       (4,'POTATO',123,3000),
       (4,'POTATO',2000,3000),
       (4,'APPLE',2000,3000),
       (5,'APPLE',360,3000),
       (5,'POTATO',2000,3000),
       (5,'TOMATO',360,3000),
       (5,'BANANA',123,3000),
       (5,'ORANGE',126,3000);

What I want is to find those order_nums that have different product_names so that no two product_name for one order_num should be the same . The desired result would be (2,3,5). I tried to solve this with Exists but it won't return the correct result.

SELECT *
FROM A_TEST A
WHERE EXISTS (SELECT *
              FROM A_TEST B
              WHERE A.order_num = B.order_num
              AND A.product_name <> B.product_name);

I know why this does not return the correct result. The reason is that if it finds at least one product_name which is different from another one, it will return it's order_num. I was wondering if you could suggest better solutions.

Thanks in advance

Best Answer

Classic solution:

SELECT order_num
FROM a_test
GROUP BY order_num
HAVING COUNT(DISTINCT product_name) > 1;

Custom solutions:

SELECT DISTINCT t1.order_num
FROM a_test t1
JOIN a_test t2 ON t1.order_num = t2.order_num
WHERE t1.product_name != t2.product_name;

WITH cte AS ( SELECT order_num, 
                     DENSE_RANK() OVER (PARTITION BY order_num ORDER BY product_name) dr
              FROM a_test )
SELECT DISTINCT order_num
FROM cte
WHERE dr = 2;

...

fiddle

The one that you used 'Join' , again does not return the desired result. It returns those records that have equal order_num but their product_name are different and does not return records with same order_num that their product_name s are the same . Just this – Pantea

Do you mean that you need only orders which (1) have at least 2 products, and (2) have none product twice? If so then add .. AND COUNT(DISTINCT product_name) = COUNT(product_name) to the first query.

SELECT order_num
FROM a_test
GROUP BY order_num
HAVING COUNT(DISTINCT product_name) > 1
AND COUNT(DISTINCT product_name) = COUNT(product_name);

fiddle

Related Question