Asger Blond
ab-mi at post3.tele.dk
Thu Sep 23 18:25:02 CDT 2010
John, To get you a clue what the code does, and what I did. First, when answering a question I normally make effort recreating the scenario in question. Which was what I did in this case: created a table and a query with the data provided by Paul. If you have done the same you can easily test and play around with the code example - soon you should notice what's going on. Second, step-by-step explanation of code examples may be fine for a class room, but IMO would be totally inadequate for a list like this. If someone does not understand the example or some parts of the example then I - and I'm sure most listers - will readily give an answer if asked politely. I didn't consider your first comment a polite request for explanation, rather a rant just reinforcing that you adhere to cursors. Being more reasonable in your second reply I'll supply explanation using the example from my second posting to Paul: WITH cte (RowNumber, PersonID, StartCode, EndCode) AS (SELECT ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY PersonID), PersonID, StartCode, EndCode FROM YourQuery) UPDATE cte SET StartCode = (SELECT EndCode FROM cte AS cte2 WHERE cte.RowNumber = cte2.RowNumber+1 AND cte.PersonID = cte2.PersonID) WHERE RowNumber > 1 The first part - before UPDATE - creates a Common Table Expression which you know is a kind of virtual table that can be used instead of a temporary table. In this cte I make use of the ranking function ROW_NUMBER() with the qualification PARTION BY PersonID ORDER BY PersonID to get a numbering sequence for each group (or "partition") of PersonID's. Suppose you have the following rows in the query, where I have deliberately added two more rows to Pauls query for the purpose of showing different PersonID's: PersonID Date StartCode EndCode 111111 29/09/2010 AA1 1AA BB1 1BB 111111 29/09/2010 AA2 2AA BB2 2BB 111111 29/09/2010 AA3 3AA BB3 3BB 111111 29/09/2010 AA4 4AA BB4 4BB 222222 29/09/2010 XX1 1XX YY1 1YY 222222 29/09/2010 XX2 2XX YY2 1YY With this recordset you can query the supplied cte using: WITH cte (RowNumber, PersonID, StartCode, EndCode) AS (SELECT ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY PersonID), PersonID, StartCode, EndCode FROM YourQuery) SELECT * FROM cte The result will be: RowNumber StartCode EndCode 1 AA1 1AA BB1 1BB 2 AA2 2AA BB2 2BB 3 AA3 3AA BB3 3BB 4 AA4 4AA BB4 4BB 1 XX1 1XX YY1 1YY 2 XX2 2XX YY2 2YY Now the last UPDATE part of the code use a correlated subquery that selects the EndCode of the previous row inside each PersonID and use it to replace the value of StartCode. It does this for all rows except for rows where RowNumber = 1. If this last where clause is omitted then all rows with RowNumber = 1 will get a NULL as StartCode. This might actually be wanted and can easily be achieved omitting the clause. Running the code as is will give the following records: PersonID Date StartCode EndCode 11111 29/09/2010 AA1 1AA BB1 1BB 11111 29/09/2010 BB1 1BB BB2 2BB 11111 29/09/2010 BB2 2BB BB3 3BB 11111 29/09/2010 BB3 3BB BB4 4BB 22222 29/09/2010 XX1 1XX YY1 1YY 22222 29/09/2010 YY1 1YY YY2 2YY As I read Paul's question this is what's wanted. PS: If an operation can be achieved both by a cursor and by a set based query then IMO the cursor implementation will be by far the slowest. If you disagree, John, then please supply an example. Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jwcolby Sendt: 24. september 2010 01:06 Til: Discussion concerning MS SQL Server Emne: Re: [dba-SQLServer] SQL Server 2005 - Cursors Or Not Cursors >> a clue what this code does I am not playing games, thanks. > If answering a question with an example of solution is inappropriate LOL, Asger I wasn't saying that at all. Providing an example solution is fine, providing knowledge is better. >then I don't know what the purpose of this list is. Helping people *learn*. A paragraph or two would have allowed those for who SQL is not our first language to begin to understand what you are doing. It is not like you are just doing CTEs (I am starting to use them, I love them, but it took me awhile to get comfortable with just that piece). You are also throwing in Over (I have not use that before) and row numbers (I have not used that before). All things that it would be wicked to know! But it reaches the point where there is so much new stuff that I can't get a handle on the what you are trying to do. If we understood the example why would we need the example? Could I go figure it out? Of course. Or I could throw together a cursor and move on. I would truly like to understand what you are doing. OTOH today I re-engineered a slew of C# code to break a bunch of updates (on 65 million record tables) into batches, wrapped in transactions. I also worked on a client's Access database to add filtering to a form, and I worked on another client's database to investigate a slowdown that occurred (probably hardware) that occurred back in July and they are just now complaining about. I did three zip code counts for a third client and after a 10 hour day I am not done yet. Without somebody who cares enough to explain their solution, the solution will go in the shitcan. Which would be a pity, but I don't have the time to spend hours figuring out something that I could learn in 5 minutes if you were kind enough to explain. John W. Colby www.ColbyConsulting.com On 9/23/2010 5:12 PM, Asger Blond wrote: > John, >> a clue what this code does > It simply does what Paul was asking for. > If answering a question with an example of solution is inappropriate then I don't know what the purpose of this list is. > Asger > > -----Oprindelig meddelelse----- > Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jwcolby > Sendt: 23. september 2010 20:54 > Til: Discussion concerning MS SQL Server > Emne: Re: [dba-SQLServer] SQL Server 2005 - Cursors Or Not Cursors > > OK Asger (and Francisco), but this is a perfect example of... WTF over? > > I for one haven't a clue what this code does, nor do I have a clue how to figure out what this code > does. > > If you are going to recommend code like this you need to tell us how it works, otherwise I for one > will go "ok, now where did I put that cursor..." > > > > John W. Colby > www.ColbyConsulting.com > > On 9/23/2010 12:13 PM, Asger Blond wrote: >> Paul, >> You can use a subquery to make this update. Following example uses a Common Table Expression and the Rank function Row_Number(): >> >> WITH cte (RowNumber, StartCode, EndCode) AS >> (SELECT ROW_NUMBER() OVER (ORDER BY PersonID), StartCode, EndCode FROM YourQuery) >> UPDATE cte SET StartCode = >> (SELECT EndCode FROM cte AS cte2 >> WHERE cte.RowNumber> 1 AND cte.RowNumber = cte2.RowNumber+1) >> >> Asger _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com