SQL multiple select query and intersect on the same table

Question

I am querying to get three columns (A,B,S) based on the top 200 of the Sum of third column (S) of a table (this has some condition checks).

SELECT TOP 200 PlantId, SKU, SUM(Demand)
FROM NetDemandTable
WHERE DemandWKId>=62 AND DemandWKId<=76
GROUP BY PlantId, SKU
ORDER BY SUM(Demand) DESC

The resultant Column B is considered as a unique records for the other select query which is again A,B,S columns, but with different condition and the this column B should intersect with the Results obtained in select Query 1.

SELECT PlantId, SKU, SUM(Demand)
FROM CuringNetDemandCopy
WHERE DemandWKId < 62 
GROUP BY PlantId, SKU  
ORDER BY SUM(Demand) DESC

Now the second query results should select only the entities of SKU in Column B obtained from the first query.

irakliG. · Accepted Answer · 2016-04-20 11:25:02Z

0

try this:

with  cte as 
(
SELECT TOP 200 PlantId, SKU, SUM(Demand)
FROM NetDemandTable
WHERE DemandWKId>=62 AND DemandWKId<=76
GROUP BY PlantId, SKU
)

SELECT PlantId, SKU, SUM(Demand)
FROM CuringNetDemandCopy
WHERE DemandWKId < 62  and SKU in (select distinct SKU from cte)
GROUP BY PlantId, SKU  
ORDER BY SUM(Demand) DESC

answered Apr 20, 2016 at 11:25

irakliG.

17610 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KumarHarsh Over a year ago

I think, "EXISTS" clause should be use here.BTW @Rajasekhar Kadambur,what output r u looking for.Like what is the main purpose of query 1

Collectives™ on Stack Overflow

SQL multiple select query and intersect on the same table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related