SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Avoiding cursor: Help with getting only first match after previous match


Avoiding cursor: Help with getting only first match after previous match

Author
Message
t.brown 89142
t.brown 89142
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 140
Hi I'm sure there is a better name for this problem, and its probably quite a common one with a hopefully easy solution. I can do this with a cursor - but I've learned from this site that its probably bad form.

I have 2 tables, with a matching text field - but the 2nd table contains many unwanted matches. and the matching has to go in sequence, so if a match is used, then future matches don't look prior to that for the next match.

Perhaps easier to explain with the examples data below:




CREATE TABLE #Table1 (
ID INT IDENTITY(1,1) PRIMARY KEY,
TEXT1 varchar(100) );

CREATE TABLE #Table2 (
aDate DATETIME NOT NULL,
TEXT2 varchar(100) );
CREATE CLUSTERED INDEX IX_aDate ON #Table2(aDate);

INSERT INTO #Table1 (TEXT1)
SELECT 'AAAA' UNION ALL
SELECT 'BBBB' UNION ALL
SELECT 'AAAA' UNION ALL
SELECT 'BBBB' UNION ALL
SELECT 'CCCC' UNION ALL
SELECT 'BBBB' UNION ALL
SELECT 'BBBB' UNION ALL
SELECT 'AAAA' UNION ALL
SELECT 'CCCC' UNION ALL
SELECT 'BBBB' UNION ALL
SELECT 'CCCC' ;

SELECT * FROM #Table1;

INSERT INTO #Table2 (aDate, TEXT2)
SELECT '2012-01-01 12:00:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:01:00', 'AAAA' UNION ALL
SELECT '2012-01-01 12:02:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:02:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:02:00', 'CCCC' UNION ALL
SELECT '2012-01-01 12:03:00', 'AAAA' UNION ALL
SELECT '2012-01-01 12:03:00', 'AAAA' UNION ALL
SELECT '2012-01-01 12:04:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:05:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:06:00', 'CCCC' UNION ALL
SELECT '2012-01-01 12:07:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:08:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:08:00', 'CCCC' UNION ALL
SELECT '2012-01-01 12:08:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:10:00', 'AAAA' UNION ALL
SELECT '2012-01-01 12:11:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:12:00', 'CCCC' UNION ALL
SELECT '2012-01-01 12:12:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:14:00', 'BBBB' UNION ALL
SELECT '2012-01-01 12:14:00', 'CCCC' UNION ALL
SELECT '2012-01-01 12:14:00', 'BBBB';




And the desired output is All of the Table1 records with the first match from table 2 where the match comes after the previous match.



-- Desired Result
ID Text1 aDate Text2
1 'AAAA', '2012-01-01 12:01:00', 'AAAA'
2 'BBBB', '2012-01-01 12:02:00', 'BBBB'
3 'AAAA', '2012-01-01 12:03:00', 'AAAA'
4 'BBBB', '2012-01-01 12:04:00', 'BBBB'
5 'CCCC', '2012-01-01 12:06:00', 'CCCC'
6 'BBBB', '2012-01-01 12:07:00', 'BBBB'
7 'BBBB', '2012-01-01 12:08:00', 'BBBB'
8 'AAAA', '2012-01-01 12:10:00', 'AAAA'
9 'CCCC', '2012-01-01 12:12:00', 'CCCC'
10 'BBBB', '2012-01-01 12:12:00', 'BBBB'
11 'CCCC', '2012-01-01 12:14:00', 'CCCC'



Here is my best effort so far. I know I have to do something with ranking or partitioning each subgroup of #Table2 and only selecting the first match - but the syntax eludes me.



-- First Add a ROW_Number to Table2
ALTER TABLE #Table2
ADD RowNum INT;
WITH SetRows AS (
SELECT ROW_NUMBER() OVER(ORDER BY aDate, TEXT2) As RN, aDate, Text2
FROM #Table2 )
UPDATE T2
SET RowNum = RN
FROM SetRows AS S
JOIN #Table2 AS T2 ON S.aDate = T2.aDate AND S.TEXT2 = T2.TEXT2

-- Gets too many rows but does include the required results
;WITH Numbered AS (
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS Row1, ID, TEXT1
FROM #Table1
), Numbered2 AS (
SELECT ROW_NUMBER() OVER(ORDER BY ID, aDate) AS RNMatch,
ROW_NUMBER() OVER( PARTITION BY aDate ORDER BY ID, aDate) AS PartNo, Row1, ID, TEXT1, T2.RowNum, T2.aDate, T2.TEXT2
FROM Numbered AS T1
JOIN #Table2 AS T2 ON T1.TEXT1 = T2.TEXT2
AND T2.RowNum >= Row1
)
SELECT N1.PartNo, N1.Row1, N1.ID, N1.TEXT1, N1.RowNum, N1.aDate, N1.TEXT2
FROM Numbered2 AS N1
JOIN Numbered2 AS N2 ON N1.RNMatch = N2.RNMatch AND N2.PartNo >= N1.PartNo
ORDER BY N1.ID, N2.aDate


Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63492 Visits: 17966
Excellent job posting ddl and sample data!!! I am a bit confused on what you are trying to do here though. I know you posted your desired output (another huge kudos!!!). However I can't figure out the logic here at all.

If we look just at AAA you have 3 rows in your results but I can't figure out what the logic of the value of the date is.

This query is what I was using to try to understand what you are looking for but it just doesn't quite match up.


SELECT *, ROW_NUMBER() over (partition by Text1 order by Text1) as RowNum FROM #Table1 order by TEXT1
select *, ROW_NUMBER() over (partition by Text2 order by aDate, Text2) as RowNum from #Table2 order by TEXT2



For AAA RowNum 1 = RowNum 1 from table2, so far so good.
AAA RowNum 2 = RowNum 2 from table2 ...still makes sense
AAA RowNum 3 = RowNum 4 from table2 ??? Why is this????

Then when you get to BBB the first one starts with row 2 from table 2 and then doesn't make any sense at all to me. You are getting rows 2,4,5 from table 2.

I suspect there must be some sort of reasoning here but I can't figure it out.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
drew.allen
drew.allen
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15693 Visits: 11221
The problem is that your desired results require that your TABLE1 be sorted in a specific order, but the data provided is insufficient to enforce that order. You have to remember that there is no default order by in a set.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
t.brown 89142
t.brown 89142
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 140
I'll try to express it better - but I'm beginning to think its a procedural not a set-based problem.

Take a row from #Table1
Match the first row (Minimum Date) in #Table2 on the text field call this Match1.

Take the next row from #table1
Match the first row in #Table2 on text field where (minimum date that comes after Match1) - call this Match2 on text field

Take Nth row from #Table1
Match on text field where date = minimum date that comes after matchN

All #Table1 rows should have a match.

So the rule is when a #Table2 entry has now been matched THE MATCHED ROW AND ALL PRECEEDING ROWS on #Table2 should be excluded from future matches.
t.brown 89142
t.brown 89142
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 140
drew.allen (11/15/2012)
The problem is that your desired results require that your TABLE1 be sorted in a specific order, but the data provided is insufficient to enforce that order. You have to remember that there is no default order by in a set.

Drew


Hence all the ROW_NUMBER() functions in my efforts so far.
But it doesn't matter if two #Table2 rows have the same Date and Text - as the outcome is the same whichever is chosen as the match.
t.brown 89142
t.brown 89142
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 140
OK so this may not be possible to get exact results in T-SQL, because, as pointed out above, there is no default sort, but here is the solution I'm going with as a 'best fit' so far.

*** Double Cursor Evil RBAR monstrosity warning ***




ALTER TABLE #Table2
ADD RowNum INT;
WITH SetRows AS (
SELECT ROW_NUMBER() OVER(ORDER BY aDate) As RN, aDate, Text2
FROM #Table2 )
UPDATE T2
SET RowNum = RN
FROM SetRows AS S
JOIN #Table2 AS T2 ON S.aDate = T2.aDate AND S.TEXT2 = T2.TEXT2



--- Sod it - use a cursor
DECLARE @ROW1 INT, @ID INT, @TEXT1 VARCHAR(100);
DECLARE @MatchDate DATETIME, @TEXT2 VARCHAR(100);
DECLARE @MatchPos INT;
DECLARE @RESULTS TABLE (ID INT PRIMARY KEY, TEXT1 varchar(100), aDate DATETIME, TEXT2 varchar(100), aDate2 DATETIME );
INSERT INTO @RESULTS (ID, TEXT1)
SELECT ID, TEXT1
FROM #Table1;
DECLARE SODIT CURSOR FAST_FORWARD FOR
SELECT ROW_NUMBER() OVER(ORDER BY T1.ID) AS Row1, T1.ID, T1.TEXT1
FROM #Table1 AS T1
ORDER BY T1.ID

OPEN SODIT;
SET @MatchDate = '2000-01-01'; -- just a minimum date
SET @MatchPos = -1;
FETCH NEXT FROM SODIT INTO @ROW1, @ID, @TEXT1
WHILE(@@FETCH_STATUS = 0)
BEGIN

SELECT @MatchPos = MIN(T2.RowNum)
FROM #Table2 AS T2
WHERE T2.TEXT2 = @TEXT1
AND T2.RowNum > @MatchPos;

SELECT @MatchDate = T2.aDate
FROM #Table2 AS T2
WHERE T2.RowNum = @MatchPos;

UPDATE @RESULTS
SET TEXT1= @TEXT1,
aDate = @MatchDate
WHERE ID = @ID;

FETCH NEXT FROM SODIT INTO @ROW1, @ID, @TEXT1
END

CLOSE SODIT;
DEALLOCATE SODIT;

DECLARE SODIT CURSOR FAST_FORWARD FOR
SELECT ROW_NUMBER() OVER(ORDER BY T1.ID) AS Row1, T1.ID, T1.TEXT1
FROM #Table1 AS T1
ORDER BY T1.ID

OPEN SODIT;
SET @MatchDate = '2000-01-01'; -- just a minimum date
SET @MatchPos = -1;
FETCH NEXT FROM SODIT INTO @ROW1, @ID, @TEXT1
WHILE(@@FETCH_STATUS = 0)
BEGIN
SELECT @MatchDate = MIN(T2.aDate)
FROM #Table2 AS T2
WHERE T2.TEXT2 = @TEXT1
AND T2.aDate >= @MatchDate

UPDATE @RESULTS
SET TEXT2 = @TEXT1,
aDate2 = @MatchDate
WHERE ID = @ID;

FETCH NEXT FROM SODIT INTO @ROW1, @ID, @TEXT1
END

CLOSE SODIT;
DEALLOCATE SODIT;
SELECT * FROM @RESULTS ORDER BY ID;




Can't wait to run it on the live tables where #table1 has 105,000 rows and Table2 has 520,000 rows!
Sad
t.brown 89142
t.brown 89142
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 140
And posting that CURSOR just got me promoted to 'SSC-Enthusiastic'

The Irony Rolleyes
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18079 Visits: 6431
I'm not getting exactly the desired results you posted but maybe this will give you a hint.


;WITH CTE AS (
SELECT TOP 1 ID, TEXT1, aDate, TEXT2
FROM #Table1
JOIN #Table2 ON TEXT1 = TEXT2
UNION ALL
SELECT a.ID, a.TEXT1, b.aDate, b.TEXT2
FROM #Table1 a
JOIN CTE c ON a.ID = c.ID + 1
JOIN #Table2 b ON a.TEXT1 = b.TEXT2 AND b.aDate >= c.aDate
)
SELECT TEXT1, aDate, TEXT2
FROM (
SELECT TEXT1, aDate, TEXT2
,n=ROW_NUMBER() OVER (PARTITION BY ID ORDER BY aDate)
FROM CTE) a
WHERE n = 1
OPTION (MAXRECURSION 0)



Where the code says AND b.aDate >= c.aDate, try making that AND b.aDate > c.aDate and run it both ways. I think this is close and that the answer may lie in reinterpreting the expected results from the sample data.

Unfortunately this is probably going to be quite slow depending on the number of rows in your actual data.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
t.brown 89142
t.brown 89142
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 140
Thanks Dwain and very useful - quicker than my cursor, and it gets the same results.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217963 Visits: 41995
drew.allen (11/15/2012)
The problem is that your desired results require that your TABLE1 be sorted in a specific order, but the data provided is insufficient to enforce that order. You have to remember that there is no default order by in a set.

Drew


Take another look, Drew. He has IDENTITY columns on both tables to preserve the desired order.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search