March 23, 2012 at 6:38 am
Happy Friday Everyone
I hope that everyone is having a good day so far.
I am working on a query that I cannot seem to get.
The data is paired almost exactly the same, other than the RoleID and the CreateDate. If the Pair of ChildIDs has a StatusID of both 2 and 3, then I DO NOT want it returned in the select query.
I do want to select the row with a ChildID that does not have another ChildID the same with StatusID of 2, and also I want to return any row(s) that has a ChildID and a StatusID the same, but different CreateDate, I want the row that was inserted first, according to the CreateDate.
So in this example, I have hi-lighted the two rows that I want to select.
DECLARE @RoleRanking TABLE
(
RelationshipID int
, ParentID int
,ChildID int
,RoleID int
,StatusID int
,CreateDate DateTime
,ActionID int
)
INSERT INTO @RoleRanking
SELECT
6370,38708,38710,12,2,'2012-03-22 10:42:19.393',3416 UNION ALL SELECT
6370,38708,38710,12,3,'2012-03-22 10:52:00.687',3417 UNION ALL SELECT
[highlight=#ffff11]6371,38708,38711,10,2,'2012-03-22 11:59:51.900',3418[/highlight] UNION ALL SELECT
6371,38708,38711,10,2,'2012-03-22 12:03:32.877',3419 UNION ALL SELECT
6372,38708,38712,12,2,'2012-03-22 12:38:04.763',3420 UNION ALL SELECT
6372,38708,38712,12,3,'2012-03-22 12:48:07.770',3421 UNION ALL SELECT
[highlight=#ffff11]6374,38708,38715,10,2,'2012-03-22 14:27:12.577',3423[/highlight]
SELECT * FROM @RoleRanking
ORDER BY CreateDate, ChildID, RoleID, StatusID ASC
Thank You in advance for any and all help or suggestions.
Andrew SQLDBA
March 23, 2012 at 7:02 am
Hello Everyone
Since the Highlight did not work well, I have added a column to the sample code to help.
DECLARE @RoleRanking TABLE
(
RelationshipID int
, ParentID int
,ChildID int
,RoleID int
,StatusID int
,CreateDate DateTime
,ActionID int
,SelectOrNot varchar(20)
)
INSERT INTO @RoleRanking
SELECT
6370,38708,38710,12,2,'2012-03-22 10:42:19.393',3416,'' UNION ALL SELECT
6370,38708,38710,12,3,'2012-03-22 10:52:00.687',3417,'' UNION ALL SELECT
6371,38708,38711,10,2,'2012-03-22 11:59:51.900',3418,'Want This Row' UNION ALL SELECT
6371,38708,38711,10,2,'2012-03-22 12:05:32.877',3419,'' UNION ALL SELECT
6372,38708,38712,12,2,'2012-03-22 12:38:04.763',3420,'' UNION ALL SELECT
6372,38708,38712,12,3,'2012-03-22 12:48:07.770',3421,'' UNION ALL SELECT
6374,38708,38715,10,2,'2012-03-22 14:27:12.577',3423,'Want This Row'
SELECT * FROM @RoleRanking
ORDER BY CreateDate, ChildID, RoleID, StatusID ASC
Thank You in advance for all your help
Andrew SQLDBA
March 23, 2012 at 8:58 am
interesting problem, I think windowed functions work well here:
SELECT RelationshipID
, ParentID
, ChildID
, RoleID
, StatusID
, CreateDate
, ActionID
, SelectOrNot
FROM (
SELECT RelationshipID
, ParentID
, ChildID
, RoleID
, StatusID
, CreateDate
, ActionID
, SelectOrNot
, ROW_NUMBER() OVER (PARTITION BY ChildID ORDER BY CreateDate) AS sort
, MAX(CASE WHEN StatusID = 2 THEN 1 ELSE 0 END) OVER (PARTITION BY ChildID) AS is_status2
, MAX(CASE WHEN StatusID = 3 THEN 1 ELSE 0 END) OVER (PARTITION BY ChildID) AS is_status3
FROM @RoleRanking) sq
WHERE sort = 1 AND NOT (is_status2 = 1 AND is_status3 = 1)
if you need to, you could easily change the 3 PARTITION BY clauses to include other columns in addition to ChildID
March 23, 2012 at 9:20 am
Chris Harshman (3/23/2012)
interesting problem, I think windowed functions work well here:
I've found that the windowed aggregates do a LOT of reads. You might be better off using a NOT EXISTS condition in the WHERE clause of your CTE.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 23, 2012 at 9:29 am
Here is the version using NOT EXISTS.
WITH sq AS (
SELECT
RelationshipID
,ParentID
,ChildID
,RoleID
,StatusID
,CreateDate
,ActionID
,SelectOrNot
,ROW_NUMBER() OVER (PARTITION BY ChildID ORDER BY CreateDate) AS sort
FROM @RoleRanking AS rr
WHERE NOT EXISTS (
SELECT *
FROM @RoleRanking AS sub
WHERE sub.ChildID = rr.ChildID
AND StatusID = 3
)
)
SELECT *
FROM sq
WHERE Sort = 1
And here are the stats:
Windowed Aggregates
Table 'Worktable'. Scan count 3, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#016C9082'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
NOT EXISTS
Table '#016C9082'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 23, 2012 at 9:36 am
Thanks Everyone,
I think we got it figured out.
Thank you to all. Hope that you have a great weekend
Andrew SQLDBA
March 23, 2012 at 9:36 am
Couple of ways: -
SELECT RelationshipID, ParentID, ChildID, RoleID, StatusID,
CreateDate, ActionID
FROM (SELECT a.RelationshipID, a.ParentID, a.ChildID, a.RoleID, a.StatusID,
a.CreateDate, a.ActionID,
ROW_NUMBER() OVER (PARTITION BY a.RelationshipID, a.ParentID, a.ChildID, a.RoleID, a.StatusID
ORDER BY a.CreateDate) AS rn
FROM @RoleRanking a
OUTER APPLY (SELECT TOP 1 RelationshipID, ParentID, ChildID, RoleID, StatusID,
CreateDate, ActionID
FROM @RoleRanking
WHERE StatusID = 2 AND a.RelationshipID = RelationshipID
AND a.RoleID = RoleID
ORDER BY CreateDate) b
OUTER APPLY (SELECT TOP 1 RelationshipID, ParentID, ChildID, RoleID, StatusID,
CreateDate, ActionID
FROM @RoleRanking
WHERE StatusID = 3 AND a.RelationshipID = RelationshipID
AND a.RoleID = RoleID
ORDER BY CreateDate) c
WHERE b.RelationshipID IS NULL OR c.RelationshipID IS NULL) a
WHERE rn = 1;
The above takes a bit too long. So I tried this: -
SELECT RelationshipID, ParentID, ChildID, RoleID,
MIN(StatusID) AS StatusID, MIN(CreateDate) AS CreateDate,
MIN(ActionID) AS ActionID
FROM (SELECT RelationshipID, ParentID, ChildID, RoleID, StatusID,
CreateDate, ActionID,
ROW_NUMBER() OVER (PARTITION BY RelationshipID, ParentID, ChildID, RoleID
ORDER BY CreateDate) AS rn
FROM (SELECT RelationshipID, ParentID, ChildID, RoleID, StatusID,
MIN(CreateDate) AS CreateDate, MIN(ActionID) AS ActionID
FROM @RoleRanking
GROUP BY RelationshipID, ParentID, ChildID, RoleID, StatusID) a
) b
GROUP BY RelationshipID, ParentID, ChildID, RoleID
HAVING MAX(rn) = 1;
Works on your sample data, but I'd test it thoroughly, something feels wrong about it.
Here's a comparison of the IO of the above two methods: -
Table '#740F363E'. Scan count 10, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
Table '#740F363E'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply