Filter challenge
-
ColdCoffe... almost there, but does not work for example GroupID 5.
CREATE TABLE #MyCodes
(
MyNo int,
GroupID varchar(100),
Code varchar(6),
MyType varchar (15)
)
INSERT INTO #MyCodes VALUES (1, 3, 10, 'Red')
INSERT INTO #MyCodes VALUES (2, 3, 11, 'Red')
INSERT INTO #MyCodes VALUES (3, 3, 11, 'Red')
INSERT INTO #MyCodes VALUES (4, 3, 15, 'Blue')
INSERT INTO #MyCodes VALUES (5, 4, 10, 'Red')
INSERT INTO #MyCodes VALUES (6, 4, 11, 'Red')
INSERT INTO #MyCodes VALUES (7, 4, 15, 'Blue')
INSERT INTO #MyCodes VALUES (8, 4, 11, 'Blue')
INSERT INTO #MyCodes VALUES (9,5,11,'Red')
INSERT INTO #MyCodes VALUES (10,5,13,'Red')
INSERT INTO #MyCodes VALUES (11,5,11,'Brown')
INSERT INTO #MyCodes VALUES (12,5,10,'Red')
INSERT INTO #MyCodes VALUES (13,5,11,'Red')
INSERT INTO #MyCodes VALUES (14,5,12,'Blue')
INSERT INTO #MyCodes VALUES (15,5,10,'Brown')
-
Okay... so we want to see any group with two or more types that have two or more rows per type.
Now tell us again how codes fit into that. I assume that we are still doing our counts within a single group ID. But do we just eliminate codes that have only a single row within the group, or do we eliminate groups that don't have at least two sets of codes with a count of two or more?
Hi Dixie,We need to eliminate groups that don't have at least two sets of types. We need codes but we do not need to do anything with them because they can be anything. We do not have a pattern with them.
-
So this original requirement is off the table now?
where count of Code >=2 group by GroupID
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Yes, I am thinking to work on one step at a time.
Right now, I am in urgent need to get 2 or more same instances of the Type by the GroupID as per sample data.
Once I have the Type working, then I will worry about the code.
Thank you for your help!
-
The code below will return only rows 5,6,7,8 from the sample table.
; with SampleTable AS
(
SELECT *
FROM
( VALUES
(1, 3, 10, 'Red')
,(2, 3, 11, 'Red')
,(3, 3, 11, 'Red')
,(4, 3, 15, 'Blue')
,(5, 4, 10, 'Red')
,(6, 4, 11, 'Red')
,(7, 4, 15, 'Blue')
,(8, 4, 11, 'Blue')
,(9, 4, 99, 'Green') -- added
) A ( rowNo , groupID, code, type_)
)
-- significant code starts here
-- first identify all types that have two or more rows within a group
,cte1 as (select groupID,type_ from SampleTable group by groupID,type_ having COUNT(*) >= 2)
-- include only rows that meet the criteria from cte1 (join)
-- AND belong to a group that has at least two rows in cte1 (where)
select t.*
from cte1 c
join SampleTable t on c.groupID = t.groupID and c.type_ = t.type_
where t.groupID in (select groupID from cte1 group by groupID having COUNT(*) >=2)
order by rowNo
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Hi Dixie,
Yes, this is working, and thank you for the explanation.
It makes sence. I really appreciate your help on this one!
John
-
Also:
CREATE TABLE #MyCodes
(
MyNoINTEGER NOT NULL,
GroupIDVARCHAR(100) NOT NULL,
CodeVARCHAR(6) NOT NULL,
MyTypeVARCHAR(15) NOT NULL,
PRIMARY KEY (GroupID, MyType, MyNo)
);
INSERT INTO #MyCodes VALUES (1, 3, 10, 'Red')
INSERT INTO #MyCodes VALUES (2, 3, 11, 'Red')
INSERT INTO #MyCodes VALUES (3, 3, 11, 'Red')
INSERT INTO #MyCodes VALUES (4, 3, 15, 'Blue')
INSERT INTO #MyCodes VALUES (5, 4, 10, 'Red')
INSERT INTO #MyCodes VALUES (6, 4, 11, 'Red')
INSERT INTO #MyCodes VALUES (7, 4, 15, 'Blue')
INSERT INTO #MyCodes VALUES (8, 4, 11, 'Blue')
INSERT INTO #MyCodes VALUES (9,5,11,'Red')
INSERT INTO #MyCodes VALUES (10,5,13,'Red')
INSERT INTO #MyCodes VALUES (11,5,11,'Brown')
INSERT INTO #MyCodes VALUES (12,5,10,'Red')
INSERT INTO #MyCodes VALUES (13,5,11,'Red')
INSERT INTO #MyCodes VALUES (14,5,12,'Blue')
INSERT INTO #MyCodes VALUES (15,5,10,'Brown')
SELECT *
FROM #MyCodes AS mc
WHERE EXISTS
(
SELECT 1
FROM
(
SELECT 1
FROM #MyCodes AS mc2
WHERE mc2.GroupID = mc.GroupID
GROUP BY mc2.MyType
HAVING COUNT_BIG(*) >= 2
) AS G (g)
HAVING COUNT_BIG(*) >= 2
);
-
With a single scan of the source table:
SELECT
y.MyNo,
y.GroupID,
y.Code,
y.MyType
FROM
(
SELECT
*,
distinct_types =
MAX(x.type_rank) OVER (
PARTITION BY x.GroupID),
has_2_items =
COUNT(CASE WHEN x.rn = 2 THEN 1 END) OVER (
PARTITION BY x.GroupID)
FROM
(
SELECT
*,
rn =
ROW_NUMBER() OVER (
PARTITION BY mc.GroupID, mc.MyType
ORDER BY mc.GroupID, mc.MyType),
type_rank =
DENSE_RANK() OVER (
PARTITION BY mc.GroupID
ORDER BY mc.MyType)
FROM #MyCodes AS mc
) AS x
) AS y
WHERE
y.distinct_types >= 2
AND y.has_2_items >= 2;
edit: to incorporate Bob's fix
-
Paul, I tried your code against the data from my example and no rows were returned. I think the proper WHERE clause should be
WHERE y.xr3 = 2 --- denotes at least two types within a group
AND y.xr2 = 1; --- denotes at least two rows within a group and type
I like the idea of using the windowed functions to make only one pass through the primary table, though.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
The Dixie Flatline (7/10/2011)
Paul, I tried your code against the data from my example and no rows were returned. I think the proper WHERE clause should beWHERE y.xr3 = 2 --- denotes at least two types within a group
AND y.xr2 = 1; --- denotes at least two rows within a group and type
I like the idea of using the windowed functions to make only one pass through the primary table, though.
Ah, thanks for that! Updated the post with the correction. I can't help feeling there's a more natural (or compact) way to express the logic using the common sub-expression spools...perhaps I'll have another go at it later. For now, I think the EXISTS query is my favourite, from a maintainability point of view, if nothing else!
-
Please do check again ... I changed my data slightly and am seeing unexpected results with the new WHERE clause. But you are definitely on the right track.
Having rechecked, this looks to be working. The xr3 test needed to be GTE, not just equals.
WHERE y.xr3 >= 2 --- denotes at least two types within a group
AND y.xr2 = 1; --- denotes at least two rows within a group and type
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Hi Bob,
While you were checking that, I changed the code a bit to make more sense (to me). Does my edited version work correctly with your data?
-
Yes, it does.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
The Dixie Flatline (7/10/2011)
Yes, it does.Awesome. All we need now is Jeff Moden to build us a million-row test table to see whether all the effort to get a single-scan plan was worthwhile! I suspect not 😀
Thanks for finding that error, Bob.
-
I may have to set up a test tonight. I'm curious about how the sorts play out as the volume ramps up.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply