InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 20:10:42 GMT20http://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspx[quote][b]t.brown 89142 (11/20/2012)[/b][hr][quote][b]CELKO (11/19/2012)[/b][hr][quote] ......[i]Common J.Celco rant[/i]...[/quote]Thank you for your assessment ... Now go back and hide under your bridge.[/quote]@t.browmHere I may disappoint you, he must be rich enough to live on his own private island, somewhere in Austin. Although, he might have some bridges there...:hehe:Tue, 20 Nov 2012 03:50:53 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspx[quote][b]CELKO (11/19/2012)[/b][hr][quote] Actually #Table2 has the date, but as its only to the minute, there are duplicates - and the order of TEXT2 does not help - see the last few lines of the sample data - and my added column RowNum has duplicates. [/quote]Duplicates? then it is not a table by defintion because it cannot have a key. Oh, IDENTITY cannot be a valid key for table either. But it sure makes you magetnic tape file programmers feel better since it lets you find[b] physical insertion order. [/b][quote]I had assumed (incorrectly as it turns out) that this problem would have a standard SQL solution as its a classic two file problem read file A, go down file B looking for a match. Save position in file B, read next from A, continue down file B from saved position. I've done this kind of thing in procedural languages many times.[/quote]EXACTLY! Your mindset is still stuck writing sequential mag tape code in a set-oriented declarative world. A read/write head position, local variables to hold changing data, etc. You are the flat earth kid in a Geography class :-D! You need to get a book on RDBMS and read before you try to program again. What you got here is a pile of stinking kludges that only trap you into that old mindset. Why don't you take a day or two and try to do this with set-oriented programming and post it? The DDL should have keys, constraints, Declarative Referential Integrity, and good data types. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. [/quote]Thank you for your assessment ... Now go back and hide under your bridge.Tue, 20 Nov 2012 01:32:11 GMTt.brown 89142http://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspxIt seems that CELKO missed his meds again. The way to educate people is not to berate them. You could bring so much knowledge and understanding to the community if you would attempt to help people learn instead of belittling their intelligence. Try posting an alternative to their solution instead of just insulting their approach and telling them how horrible their approach is. I know I would love to see [i]your[/i] approach to this type of problem.Mon, 19 Nov 2012 12:24:26 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspx[quote][b]CELKO (11/19/2012)[/b][hr][quote] ... Oh, IDENTITY cannot be a valid key for table either. ... [/quote]Sorry to disappointing you again, but it really can!Have you tried it? If you need our help we can show how it does work...Just in case:[quote]IDENTITYIndicates that the new column is an identity column. When a new row is added to the table, the Database Engine provides a unique, incremental value for the column. [b]Identity columns are typically used with PRIMARY KEY constraints to serve as the unique row identifier for the table.[/b][/quote]from[url]http://msdn.microsoft.com/en-us/library/ms174979.aspx[/url]Mon, 19 Nov 2012 11:51:38 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspx[quote] Actually #Table2 has the date, but as its only to the minute, there are duplicates - and the order of TEXT2 does not help - see the last few lines of the sample data - and my added column RowNum has duplicates. [/quote]Duplicates? then it is not a table by defintion because it cannot have a key. Oh, IDENTITY cannot be a valid key for table either. But it sure makes you magetnic tape file programmers feel better since it lets you find[b] physical insertion order. [/b][quote]I had assumed (incorrectly as it turns out) that this problem would have a standard SQL solution as its a classic two file problem read file A, go down file B looking for a match. Save position in file B, read next from A, continue down file B from saved position. I've done this kind of thing in procedural languages many times.[/quote]EXACTLY! Your mindset is still stuck writing sequential mag tape code in a set-oriented declarative world. A read/write head position, local variables to hold changing data, etc. You are the flat earth kid in a Geography class :-D! You need to get a book on RDBMS and read before you try to program again. What you got here is a pile of stinking kludges that only trap you into that old mindset. Why don't you take a day or two and try to do this with set-oriented programming and post it? The DDL should have keys, constraints, Declarative Referential Integrity, and good data types. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. Mon, 19 Nov 2012 11:39:23 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspx[quote][b]thava (11/19/2012)[/b][hr][quote][b]dwain.c (11/15/2012)[/b][hr]... SNIP Where the code says AND b.aDate >= c.aDate, try making that AND b.aDate > c.aDate and [b]run it both ways. [/b]... SNIP[/quote]just by curiosity the last row of the result is not matched so i think adding an identity to the #table2 and using that will solve this issue may i right or we will need to work more[/quote]For each item ONE of the two runs gets the correct result - where the differences occur required manual inspection to choose the right one. - But it happened a lot less than you'd think for the real data - less than 200 manual checks out of around 80,000 matches.Mon, 19 Nov 2012 06:16:46 GMTt.brown 89142http://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspx[quote][b]dwain.c (11/15/2012)[/b][hr]I'm not getting exactly the desired results you posted but maybe this will give you a hint.[code="sql"];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, TEXT2FROM ( SELECT TEXT1, aDate, TEXT2 ,n=ROW_NUMBER() OVER (PARTITION BY ID ORDER BY aDate) FROM CTE) aWHERE n = 1OPTION (MAXRECURSION 0)[/code]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.[/quote]just by curiosity the last row of the result is not matched so i think adding an identity to the #table2 and using that will solve this issue may i right or we will need to work moreMon, 19 Nov 2012 05:59:20 GMTthavahttp://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspx[quote][b]t.brown 89142 (11/16/2012)[/b][hr]I had assumed (incorrectly as it turns out) that this problem would have a standard SQL solution as its a classic two file problem read file A, go down file B looking for a match. Save position in file B, read next from A, continue down file B from saved position. I've done this kind of thing in procedural languages many times.[/quote]Probably the reason there is no "standard" SQL solution for this is that SQL isn't procedural by nature (it is declarative) but you can do many procedural things with it.I'm glad it worked for you even though I had my concerns on how it might perform over larger row sets.Sun, 18 Nov 2012 17:34:29 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspxActually #Table2 has the date, but as its only to the minute, there are duplicates - and the order of TEXT2 does not help - see the last few lines of the sample data - and my added column RowNum has duplicates. I think it originally comes from a text file somewhere, I could ask the programmer to re-import it for me, adding an identity column - trouble is he's run off his feet. But the 'best fit' solution devised by my cursor and replicated by Dwain's recursive cte is good enough for the end user. I had assumed (incorrectly as it turns out) that this problem would have a standard SQL solution as its a classic two file problem read file A, go down file B looking for a match. Save position in file B, read next from A, continue down file B from saved position. I've done this kind of thing in procedural languages many times.Fri, 16 Nov 2012 08:42:54 GMTt.brown 89142http://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspx[quote][b]t.brown 89142 (11/15/2012)[/b][hr][quote][b]drew.allen (11/15/2012)[/b][hr]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[/quote]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.[/quote]But you DO have something to guarantee the order you want in the form of IDENTITY columns.Fri, 16 Nov 2012 07:45:26 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspx[quote][b]drew.allen (11/15/2012)[/b][hr]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[/quote]Take another look, Drew. He has IDENTITY columns on both tables to preserve the desired order.Fri, 16 Nov 2012 07:44:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspxThanks Dwain and very useful - quicker than my cursor, and it gets the same results.Fri, 16 Nov 2012 06:37:39 GMTt.brown 89142http://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspxI'm not getting exactly the desired results you posted but maybe this will give you a hint.[code="sql"];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, TEXT2FROM ( SELECT TEXT1, aDate, TEXT2 ,n=ROW_NUMBER() OVER (PARTITION BY ID ORDER BY aDate) FROM CTE) aWHERE n = 1OPTION (MAXRECURSION 0)[/code]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.Thu, 15 Nov 2012 18:28:15 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspxAnd posting that CURSOR just got me promoted to 'SSC-Enthusiastic' The Irony :rolleyes:Thu, 15 Nov 2012 09:15:34 GMTt.brown 89142http://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspxOK 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 ***[code="sql"]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 = RNFROM SetRows AS S JOIN #Table2 AS T2 ON S.aDate = T2.aDate AND S.TEXT2 = T2.TEXT2 --- Sod it - use a cursorDECLARE @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.IDOPEN SODIT;SET @MatchDate = '2000-01-01'; -- just a minimum dateSET @MatchPos = -1;FETCH NEXT FROM SODIT INTO @ROW1, @ID, @TEXT1WHILE(@@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, @TEXT1ENDCLOSE 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.IDOPEN SODIT;SET @MatchDate = '2000-01-01'; -- just a minimum dateSET @MatchPos = -1;FETCH NEXT FROM SODIT INTO @ROW1, @ID, @TEXT1WHILE(@@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, @TEXT1ENDCLOSE SODIT;DEALLOCATE SODIT;SELECT * FROM @RESULTS ORDER BY ID;[/code]Can't wait to run it on the live tables where #table1 has 105,000 rows and Table2 has 520,000 rows!:(Thu, 15 Nov 2012 09:12:55 GMTt.brown 89142http://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspx[quote][b]drew.allen (11/15/2012)[/b][hr]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[/quote]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.Thu, 15 Nov 2012 08:18:50 GMTt.brown 89142http://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspxI'll try to express it better - but I'm beginning to think its a procedural not a set-based problem.Take a row from #Table1Match the first row (Minimum Date) in #Table2 on the text field call this Match1. Take the next row from #table1Match 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 #Table1Match on text field where date = minimum date that comes after matchNAll #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.Thu, 15 Nov 2012 08:16:14 GMTt.brown 89142http://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspxThe 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.DrewThu, 15 Nov 2012 08:15:46 GMTdrew.allenhttp://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspxExcellent 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.[code]SELECT *, ROW_NUMBER() over (partition by Text1 order by Text1) as RowNum FROM #Table1 order by TEXT1select *, ROW_NUMBER() over (partition by Text2 order by aDate, Text2) as RowNum from #Table2 order by TEXT2[/code]For AAA RowNum 1 = RowNum 1 from table2, so far so good.AAA RowNum 2 = RowNum 2 from table2 ...still makes senseAAA 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.Thu, 15 Nov 2012 07:48:41 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1385085-392-1.aspxHi 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:[code="sql"]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 ALLSELECT 'BBBB' UNION ALL SELECT 'AAAA' UNION ALLSELECT 'BBBB' UNION ALLSELECT 'CCCC' UNION ALLSELECT 'BBBB' UNION ALLSELECT 'BBBB' UNION ALLSELECT 'AAAA' UNION ALLSELECT 'CCCC' UNION ALLSELECT 'BBBB' UNION ALLSELECT 'CCCC' ;SELECT * FROM #Table1;INSERT INTO #Table2 (aDate, TEXT2)SELECT '2012-01-01 12:00:00', 'BBBB' UNION ALLSELECT '2012-01-01 12:01:00', 'AAAA' UNION ALLSELECT '2012-01-01 12:02:00', 'BBBB' UNION ALLSELECT '2012-01-01 12:02:00', 'BBBB' UNION ALLSELECT '2012-01-01 12:02:00', 'CCCC' UNION ALLSELECT '2012-01-01 12:03:00', 'AAAA' UNION ALLSELECT '2012-01-01 12:03:00', 'AAAA' UNION ALLSELECT '2012-01-01 12:04:00', 'BBBB' UNION ALLSELECT '2012-01-01 12:05:00', 'BBBB' UNION ALLSELECT '2012-01-01 12:06:00', 'CCCC' UNION ALLSELECT '2012-01-01 12:07:00', 'BBBB' UNION ALLSELECT '2012-01-01 12:08:00', 'BBBB' UNION ALLSELECT '2012-01-01 12:08:00', 'CCCC' UNION ALLSELECT '2012-01-01 12:08:00', 'BBBB' UNION ALLSELECT '2012-01-01 12:10:00', 'AAAA' UNION ALLSELECT '2012-01-01 12:11:00', 'BBBB' UNION ALLSELECT '2012-01-01 12:12:00', 'CCCC' UNION ALLSELECT '2012-01-01 12:12:00', 'BBBB' UNION ALLSELECT '2012-01-01 12:14:00', 'BBBB' UNION ALLSELECT '2012-01-01 12:14:00', 'CCCC' UNION ALLSELECT '2012-01-01 12:14:00', 'BBBB';[/code]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.[code="other"]-- Desired ResultID Text1 aDate Text21 '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' [/code]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.[code="sql"]-- 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 = RNFROM 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 N1JOIN Numbered2 AS N2 ON N1.RNMatch = N2.RNMatch AND N2.PartNo >= N1.PartNoORDER BY N1.ID, N2.aDate [/code]Thu, 15 Nov 2012 04:38:57 GMTt.brown 89142