Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Avoiding cursor: Help with getting only first match after previous match Expand / Collapse
Author
Message
Posted Friday, November 16, 2012 7:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
t.brown 89142 (11/15/2012)
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.


But you DO have something to guarantee the order you want in the form of IDENTITY columns.


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1385714
Posted Friday, November 16, 2012 8:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:02 AM
Points: 114, Visits: 140
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. 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.
Post #1385730
Posted Sunday, November 18, 2012 5:34 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
t.brown 89142 (11/16/2012)


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.


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.



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!
Post #1386114
Posted Monday, November 19, 2012 5:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:02 AM
Points: 213, Visits: 400
dwain.c (11/15/2012)
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.


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
Post #1386332
Posted Monday, November 19, 2012 6:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:02 AM
Points: 114, Visits: 140
thava (11/19/2012)
dwain.c (11/15/2012)
... SNIP

Where the code says AND b.aDate >= c.aDate, try making that AND b.aDate > c.aDate and run it both ways.

... SNIP



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


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.
Post #1386340
Posted Monday, November 19, 2012 11:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
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.


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 physical insertion order.

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.


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 !

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.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1386523
Posted Monday, November 19, 2012 11:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
CELKO (11/19/2012)
[quote]
...
Oh, IDENTITY cannot be a valid key for table either.
...


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:

IDENTITY
Indicates 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. Identity columns are typically used with PRIMARY KEY constraints to serve as the unique row identifier for the table.

from
http://msdn.microsoft.com/en-us/library/ms174979.aspx



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1386529
Posted Monday, November 19, 2012 12:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 11,970, Visits: 10,995
It 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 your approach to this type of problem.


_______________________________________________________________

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 Moden's 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)
Post #1386539
Posted Tuesday, November 20, 2012 1:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:02 AM
Points: 114, Visits: 140
CELKO (11/19/2012)
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.


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 physical insertion order.

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.


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 !

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.



Thank you for your assessment ...
Now go back and hide under your bridge.

Post #1386720
Posted Tuesday, November 20, 2012 3:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
t.brown 89142 (11/20/2012)
CELKO (11/19/2012)
[quote] ...
...
Common J.Celco rant
...


Thank you for your assessment ...
Now go back and hide under your bridge.



@t.browm
Here 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...


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1386803
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse