jwcolby
jwcolby at colbyconsulting.com
Thu Sep 23 19:12:59 CDT 2010
> I didn't consider your first comment a polite request for explanation, rather a rant 1) A rant it was. I have work to do, I am not here to play silly games. Clues? I think not. >just reinforcing that you adhere to cursors. 2) I don't "adhere" to cursors, I use the tools I know and can understand. I have probably used a cursor a handful of times over the years. This KIND of things is exactly where I would use it, getting information from a different row to use in the current row. If you will teach me a better tool, I will most certainly use that instead. But your example (sans explanation) did not teach me anything. > 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. And why is that. I have been on this and the Access and VB lists for 15 years and we do give step by step explanations of code. Why would you not? This *is* a classroom. Why do you think people come here? BTW, just an FYI, a year or two ago (over on the Access list) I wrote an entire series of emails on Access classes and withevents. It is a complex subject and needed more than an example. And I never once told the reader to go RTFM. I put my money where my mouth is. >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. <rant mode> I was not being rude, I was saying what I needed to say. I repeat, if I understood the example, then I wouldn't need you to write the example. By definition if you have to provide me an example then you need to explain it. Or if you know of examples on the internet that explain it provide those. Look at any book on technical things like SQL or C#. They don't just throw out examples, they discuss the principals, then they provide examples, then they explain how the example applies to the principal. Asger, I absolutely appreciate your taking the time. I really do. What I *don't* appreciate is the "Read the BOL" attitude. I probably own as many books as anyone on this list, SHELVES full of them. But I work for a living, and my job is NOT as a DBA. The DBA is about 5% of my job. I am a sole proprietor who routinely uses, in any given day, SQL Server, C# and Access. I build my own computers, install software, and everything else you can think of that has to be done to keep my business running, *just me* doing the whole damned thing. I don't have time to solve "clues". I need as much help as you can give so that I can learn it from you and your examples *quickly*. If you don't want to provide that help, just say so, I can understand that. Whatever you do, *don't* say "here's an example, now go figure it out". SHITCAN! <\rant mode> And Asger, thank you very much for the detailed explanation. I will study that and understand that. John W. Colby www.ColbyConsulting.com On 9/23/2010 7:25 PM, Asger Blond wrote: > 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 > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com >