with cte_order as ( select order_id,product_id,quantity,row_number() over(partition by order_id order by product_id) as rnk from OrderDetails where product_id in(100,200) ) Select distinct order_id from cte_order group by order_id having count(distinct product_id)>=2
with cte as (select * from OrderDetails where order_id in (select order_id from OrderDetails group by order_id having count(product_id)>2)),cte2 as ( select order_id, case when product_id =100 then 1 when product_id=200 then 1 end as flg from cte),cte3 as ( select distinct order_id,sum(flg) over (partition by order_id) as sum_flg from cte2 ) select distinct order_id from cte3 where sum_flg>=2
with cte as( select order_id, COUNT(CASE when Product_id=100 THEN 1 ELSE NULL END) as 100_count, COUNT(CASE when Product_id=200 THEN 1 ELSE NULL END) as 200_count FROM OrderDetails GROUP BY order_id ) select order_id FROM cte where 100_count>=1 and 200_count>=1;
WITH CTE AS(SELECT DISTINCT order_id,product_id, CASE WHEN product_id = 100 OR product_id = 200 THEN 'YES' ELSE 'NO' END AS STAT from OrderDetails) SELECT order_id,count(product_id) AS distinct_products FROM CTE WHERE STAT = 'YES' group by 1 having count(product_id) > 1;
Initially i thought your question is find order_id who order product_id each time unique product but they also ordered atleast 100,200 product_id that's why i write this way and output is order_id 1,4 with cte as ( select * from OrderDetails where order_id in (select order_id from OrderDetails where product_id in(100,200) group by order_id having count(distinct product_id) = 2) ),cte2 as ( select order_id, row_number()over(partition by order_id order by product_id) -dense_rank()over(partition by order_id order by product_id) as diff from cte ) select order_id from cte2 group by order_id having sum(diff) = 0
select order_id from( select order_id,product_id,count(product_id)over(partition by order_id order by product_id)ranks from orderdetails where product_id =100 or product_id=200 group by order_id,product_id )t1 where t1.ranks=2
my attempt on SQL server: ============================= with collated_products as (select order_id, STRING_AGG(product_id, ',') within group (order by product_id) [products] from (select distinct order_id, product_id from OrderDetails ) s1 group by order_id) select order_id from collated_products where [products] like '100,200%' =============================
hello 1 solution using exists operator select distinct order_id from OrderDetails od where product_id=100 and exists (select * from orderdetails where product_id=200 and od.order_id=order_id) 2nd solution with window functions with flo as ( select order_id, count(count(distinct case when product_id in (100,200) then 1 else 0 end))over(partition by order_id )as both_products from orderdetails where product_id in (100,200) group by order_id, product_id ) select distinct order_id from flo where both_products=2
select order_id from OrderDetails
where product_id in(100,200)
group by order_id
having count(distinct product_id) = 2
great
with cte_order as
(
select order_id,product_id,quantity,row_number() over(partition by order_id order by product_id) as rnk from OrderDetails where product_id in(100,200)
)
Select distinct order_id from cte_order group by order_id having count(distinct product_id)>=2
with cte as (select *
from OrderDetails where order_id in (select order_id from OrderDetails group by order_id having count(product_id)>2)),cte2 as (
select order_id,
case when product_id =100 then 1
when product_id=200 then 1 end as flg
from cte),cte3 as (
select distinct order_id,sum(flg) over (partition by order_id) as sum_flg from cte2 )
select distinct order_id from cte3 where sum_flg>=2
with cte as(
select order_id,
COUNT(CASE when Product_id=100 THEN 1 ELSE NULL END) as 100_count,
COUNT(CASE when Product_id=200 THEN 1 ELSE NULL END) as 200_count
FROM OrderDetails GROUP BY order_id
)
select order_id FROM cte where 100_count>=1 and 200_count>=1;
WITH CTE AS(SELECT DISTINCT order_id,product_id,
CASE WHEN product_id = 100 OR product_id = 200 THEN 'YES' ELSE 'NO' END AS STAT
from OrderDetails)
SELECT order_id,count(product_id) AS distinct_products
FROM CTE
WHERE STAT = 'YES'
group by 1
having count(product_id) > 1;
Initially i thought your question is find order_id who order product_id each time unique product but they also ordered atleast 100,200 product_id that's why i write this way and output is order_id 1,4
with cte as
(
select * from OrderDetails
where order_id in (select order_id from OrderDetails
where product_id in(100,200)
group by order_id
having count(distinct product_id) = 2)
),cte2 as
(
select order_id,
row_number()over(partition by order_id order by product_id)
-dense_rank()over(partition by order_id order by product_id) as diff
from cte
) select order_id
from cte2
group by order_id
having sum(diff) = 0
select order_id from(
select order_id,product_id,count(product_id)over(partition by order_id order by product_id)ranks from orderdetails
where product_id =100 or product_id=200 group by order_id,product_id )t1 where t1.ranks=2
my attempt on SQL server:
=============================
with collated_products as (select order_id, STRING_AGG(product_id, ',') within group (order by product_id) [products]
from (select distinct order_id, product_id
from OrderDetails
) s1
group by order_id)
select order_id
from collated_products
where [products] like '100,200%'
=============================
hello
1 solution using exists operator
select distinct order_id
from OrderDetails od
where product_id=100
and exists (select * from orderdetails where product_id=200 and od.order_id=order_id)
2nd solution with window functions
with flo as (
select order_id, count(count(distinct case when product_id in (100,200) then 1 else 0 end))over(partition by order_id )as both_products
from orderdetails
where product_id in (100,200)
group by order_id, product_id
)
select distinct order_id
from flo
where both_products=2