jwcolby
jwcolby at colbyconsulting.com
Thu Sep 23 18:06:01 CDT 2010
>> 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