x

how to collect first row values into the second row

I am using sql2005 how to collect the first row values without using lead funtion

what is equal to lead funtion in sql server 2005

more ▼

asked Mar 14, 2012 at 06:01 PM in Default

avatar image

jkchowdary
1 2 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Mr. Riley had an excellent answer to that in a similar question here.

more ▼

answered Mar 14, 2012 at 08:15 PM

avatar image

Mark
2.6k 24 27 31

I think the OP's question was to find the previous rows values e.g. running total.

Mar 15, 2012 at 06:51 AM Usman Butt

Ha! I think you're right. Obviously, I am not an Oracle developer.

Mar 15, 2012 at 02:04 PM Mark

BTW, Lead function is now available with SQL Server 2012 :)

Mar 15, 2012 at 02:15 PM Usman Butt

There was a similar question in the past here. @WilliamDurkin answered using recursive CTE and I posted an answer using the quirky update which is a very fast method of calculating the running totals. Of course in SQL Server 2012 both LEAD and LAG are available as @Usman already mentioned, but in earlier versions the quirky update is a very good method to use. Here is the link:

http://ask.sqlservercentral.com/questions/47318/how-to-calculate-balance-on-the-bases-of-previous.html

Additionally, there is an excellent article by Jeff Moden titled Solving the Running Total and Ordinal Rank Problems (Rewritten). Please read it.

There is another very simple way, which consists of including the row_number() into select (partitioned on per account basis) and then it is possible to left join the result with itself on the "off-by-one" basis to have the values from the previous row available to the current row. This method is not as fast as quirky update but works well with record counts in 6 digits or less.

Oleg

Mar 15, 2012 at 02:45 PM Oleg

The same question I mentioned earlier also has a very good answer by @Pavel Pawlowski which uses the CLR function and it is also smoking fast.

Mar 15, 2012 at 02:56 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2017

asked: Mar 14, 2012 at 06:01 PM

Seen: 2138 times

Last Updated: Mar 15, 2012 at 02:56 PM

Copyright 2016 Redgate Software. Privacy Policy