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
Mar 14 '12 at 06:01 PM
Mr. Riley had an excellent answer to that in a similar question here.
Mar 14 '12 at 08:15 PM
I think the OP's question was to find the previous rows values e.g. running total.
Ha! I think you're right. Obviously, I am not an Oracle developer.
BTW, Lead function is now available with SQL Server 2012 :)
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:
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.
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.
Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.
Once you sign in you will be able to subscribe for any updates here
Answers and Comments
asked: Mar 14 '12 at 06:01 PM
Seen: 833 times
Last Updated: Mar 15 '12 at 02:56 PM
When is the next version of SQL Server due?
How do I force SQL Server 2005 to run a JOIN before the WHERE?
SQL 2005 won't fully uninstall
Restoring DB to SQL Server 2005 SP3 from SQL Server SP2
confusion between in and exists
deploy SSRS reports to folders within a report server
Server crashes with high CPU
SSIS and using different Connections within a single Dataflow task
there is error of no row at o position at line no:5
The job "ASPState_Job_DeleteExpiredSessions" filling up SQL Agent job history
Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.Ask SQL Server Central is a community service provided by Red Gate.