July 8, 2012 at 7:40 pm
I a typical Customer order scenario, there is a header record and one or more lines records per header. (If you would like sample files to create a table and insert records, just ask. I am thinking this is so common that would not be necessary. But if it is needed, please let me know).
I am looking for a way to return all the orders that contain several items. Let's say the items are A,B and C. I want all order numbers that contain all three items. I do not want an order that only contains one or two of the items.
At the moment the following query does work.
Select OrdNo From OrdLine
where ItemNo = 'A'
Intersects
Select OrdNo From OrdLine
where ItemNo = 'B'
Intersects
Select OrdNo From OrdLine
where ItemNo = 'C'
Is there another way of returning the unique order numbers that contain all three Item numbers?
Thanks,
pat
July 9, 2012 at 12:27 am
If i am not Wrong, i hope this may be the query, U need
Select OrdNo From OrdLine
where ItemNo = 'A'
UNION
Select OrdNo From OrdLine
where ItemNo = 'B'
UNION
Select OrdNo From OrdLine
July 9, 2012 at 12:49 am
suggest you read the following article on this site....
http://qa.sqlservercentral.com/articles/T-SQL/88244/
hope it helps.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 9, 2012 at 1:12 am
Bhu1 (7/9/2012)
If i am not Wrong, i hope this may be the query, U needSelect OrdNo From OrdLine
where ItemNo = 'A'
UNION
Select OrdNo From OrdLine
where ItemNo = 'B'
UNION
Select OrdNo From OrdLine
Sorry, but this is wrong query. Please verify answer before creating a sample table.He needs the order those are having all three items A,B,C.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 9, 2012 at 1:20 am
mpdillon (7/8/2012)
I a typical Customer order scenario, there is a header record and one or more lines records per header. (If you would like sample files to create a table and insert records, just ask. I am thinking this is so common that would not be necessary. But if it is needed, please let me know).I am looking for a way to return all the orders that contain several items. Let's say the items are A,B and C. I want all order numbers that contain all three items. I do not want an order that only contains one or two of the items.
At the moment the following query does work.
Select OrdNo From OrdLine
where ItemNo = 'A'
Intersects
Select OrdNo From OrdLine
where ItemNo = 'B'
Intersects
Select OrdNo From OrdLine
where ItemNo = 'C'
Is there another way of returning the unique order numbers that contain all three Item numbers?
Thanks,
pat
Sorry but again what you can see we can't see at our end.
So to help you please help us by providing sample data.
The only purpose for that to know how the records reside in the table.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 9, 2012 at 1:26 am
Without some DDL and sample data I can't post a tested solution but something like this may work (INTERSECT the way you've written it should except you misspelled the keyword):
Select OrdNo
From OrdLine
where ItemNo IN ('A', 'B', 'C')
GROUP BY OrdNo, ItemNo
HAVING COUNT(ItemNo) = 3
Note that if your order can contain item A more than once, then simply COUNT the DISTINCT ItemNo.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 9, 2012 at 3:52 am
Try something like this.
select count(ordno) from
(
select OrdNo,
sum(distinct(case when ItemNo = 'A' then 1 when ItemNo = 'B' then 2 when ItemNo = 'C' then 4 end)) as status
from OrdLine
where ItemNo IN ('A', 'B', 'C')
group by OrdNo
) dta
where
1=1
--and status = 3 -- a,b but not c
--and status = 5--does have a,c but not b
--and status = 6--does have b,c but not a
and status = 7 --all 3
--and status = 0 --none of them
--and status = 1 -- just A
--and status = 2 -- just b
--and status = 4 -- just C
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 9, 2012 at 4:42 am
Gullimeel (7/9/2012)
Try something like this.
--change the id with ordno and cd with product code
select * from
(
select id,
max(case when cd = 'A' then 1 else 0 end) +
max(case when cd = 'B' then 2 else 0 end) +
max(case when cd = 'C' then 4 else 0 end) as status
from a
--where cd in ('A','B','C')
group by id
) dta
where
1=1
--and status = 3 -- a,b but not c
--and status = 5--does have a,c but not b
--and status = 6--does have b,c but not a
and status = 7 --all 3
--and status = 0 --none of them
--and status = 1 -- just A
--and status = 2 -- just b
--and status = 4 -- just C
How about the performance - better than the query posted by OP ?
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 9, 2012 at 4:43 am
Thanks for the input.
Here is the code to create a table and populate it.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OrdLine](
[OrdNo] [int] NULL,
[ItemNo] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Add data:
Insert into OrdLine (OrdNo,ItemNo)
select 1,'A'
Union all
Select 1,'B'
Union all
select 1,'C'
union all
select 2,'A'
Union all
Select 2,'B'
Union all
select 3,'C'
union all
select 4,'D'
Union all
Select 4,'B'
Union all
select 4,'C'
Thanks,
pat
July 9, 2012 at 4:45 am
Gullimeel (7/9/2012)
--------------------------------------------------------------------------------
Try something like this.
--change the id with ordno and cd with product code
select * from
(
select id,
max(case when cd = 'A' then 1 else 0 end) +
max(case when cd = 'B' then 2 else 0 end) +
max(case when cd = 'C' then 4 else 0 end) as status
from a
--where cd in ('A','B','C')
group by id
) dta
where
1=1
--and status = 3 -- a,b but not c
--and status = 5--does have a,c but not b
--and status = 6--does have b,c but not a
and status = 7 --all 3
--and status = 0 --none of them
--and status = 1 -- just A
--and status = 2 -- just b
--and status = 4 -- just C
How about the performance - better than the query posted by OP ?
--rhythmk
Give it a try..if you feel it is not performing better then let me know 🙂
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 9, 2012 at 4:47 am
A slight modification to dwain.c's solution works and seems simple enough.
--Remove ItemNo from the Group By
Select OrdNo
From OrdLine
where ItemNo IN ('A', 'B', 'C')
GROUP BY OrdNo
HAVING COUNT(ItemNo) = 3
Thank you,
pat
July 9, 2012 at 4:51 am
Gullimeel,
I receive an error, "Invalid object name 'a' ".
If the ItemNo is removed from the Group By in dwain.c's solution, the correct order number is returned.
Thanks,
pat
July 9, 2012 at 4:57 am
You have to change that query..I just used a table a.Change it as per your requirement... like a will be your table name,id will be ordno and cd will be itemno..
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 9, 2012 at 4:58 am
Try it with a million invoices (3M detail rows):
CREATE TABLE #OrdLine( [OrdNo] [int] NULL, [ItemNo] [varchar](50) NULL) ON [PRIMARY]
;WITH Tally (n) AS (
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns c1 CROSS JOIN sys.all_columns c2)
INSERT INTO #OrdLine
SELECT OrdNo=n, ItemNo
FROM Tally
CROSS APPLY (SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C') a(ItemNo)
PRINT '--- OP''s query'
SET STATISTICS TIME ON
Select OrdNo From #OrdLine
where ItemNo = 'A'
Intersect
Select OrdNo From #OrdLine
where ItemNo = 'B'
Intersect
Select OrdNo From #OrdLine
where ItemNo = 'C'
SET STATISTICS TIME OFF
PRINT '--- Dwain''s query'
SET STATISTICS TIME ON
Select OrdNo
From #OrdLine
where ItemNo IN ('A', 'B', 'C')
GROUP BY OrdNo
HAVING COUNT(ItemNo) = 3
SET STATISTICS TIME OFF
PRINT '--- Gullimeel''s query'
SET STATISTICS TIME ON
--change the id with ordno and cd with product code
select * from
(
select OrdNo,
max(case when ItemNo = 'A' then 1 else 0 end) +
max(case when ItemNo = 'B' then 2 else 0 end) +
max(case when ItemNo = 'C' then 4 else 0 end) as status
from #OrdLine
--where cd in ('A','B','C')
group by OrdNo
) dta
where
1=1
--and status = 3 -- a,b but not c
--and status = 5--does have a,c but not b
--and status = 6--does have b,c but not a
and status = 7 --all 3
--and status = 0 --none of them
--and status = 1 -- just A
--and status = 2 -- just b
--and status = 4 -- just C
SET STATISTICS TIME OFF
DROP TABLE #OrdLine
A minor fix to my query returned these results:
(3000000 row(s) affected)
--- OP's query
(1000000 row(s) affected)
SQL Server Execution Times:
CPU time = 6132 ms, elapsed time = 7305 ms.
--- Dwain's query
(1000000 row(s) affected)
SQL Server Execution Times:
CPU time = 3791 ms, elapsed time = 9540 ms.
--- Gullimeel's query
(1000000 row(s) affected)
SQL Server Execution Times:
CPU time = 5523 ms, elapsed time = 8920 ms.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 9, 2012 at 5:57 am
mpdillon (7/9/2012)
A slight modification to dwain.c's solution works and seems simple enough.
--Remove ItemNo from the Group By
Select OrdNo
From OrdLine
where ItemNo IN ('A', 'B', 'C')
GROUP BY OrdNo
HAVING COUNT(ItemNo) = 3
Thank you,
pat
As per your table DDL, use DISTINCT as suggested by dwain to avoid failure because of duplicate values.
i.e.
Select OrdNo
From OrdLine
where ItemNo IN ('A', 'B', 'C')
GROUP BY OrdNo
HAVING COUNT(DISTINCT ItemNo) = 3
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply