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

jkchowdary gravatar 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][1].

[1]: http://ask.sqlservercentral.com/questions/2623/coalesce-multiple-rows-into-a-comma-delimited-valu.html
more ▼

answered Mar 14, 2012 at 08:15 PM

Mark gravatar image

Mark
2.6k 23 25 27

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)][2]. 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

[2]: http://www.sqlservercentral.com/articles/T-SQL/68467/
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1951

asked: Mar 14, 2012 at 06:01 PM

Seen: 1764 times

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