Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

All the rows before this one Expand / Collapse
Author
Message
Posted Saturday, December 21, 2013 1:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 1,284, Visits: 610
sestell1 (12/20/2013)
LAG works for a fixed number of rows before the current row.
For all the rows prior to the current row, wouldn't you use something like ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in the OVER clause?

https://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server-part-2-the-frame/

http://sqlmag.com/sql-server-2012/how-use-microsoft-sql-server-2012s-window-functions-part-1

https://www.simple-talk.com/sql/t-sql-programming/sql-server-2012-window-function-basics/

+1 True.
Post #1525246
Posted Saturday, December 21, 2013 5:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 6,020, Visits: 8,289
So how exactly does one "aggregate (...) those rows that had been processed BEFORE the current row" with LAG?
(I emphasized two important words in that quote from the question - rows is plural and aggregate suggests aggregating multiple rows into one result).

I picked PREVIOUS, thinking that the author had misspelled PRECEDING, which would have been the only correct answer to this question.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1525259
Posted Saturday, December 21, 2013 9:25 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 4,125, Visits: 3,425
Hugo Kornelis (12/21/2013)
So how exactly does one "aggregate (...) those rows that had been processed BEFORE the current row" with LAG? . . .

http://technet.microsoft.com/en-us/library/hh231256.aspx
Post #1525265
Posted Saturday, December 21, 2013 9:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 6,020, Visits: 8,289
Revenant (12/21/2013)
Hugo Kornelis (12/21/2013)
So how exactly does one "aggregate (...) those rows that had been processed BEFORE the current row" with LAG? . . .

http://technet.microsoft.com/en-us/library/hh231256.aspx

Not sure why you post that link. I know what LAG is, and what it does.
It does not answer the question on how to aggregate rows (watch the plural) with a function that is designed to return the value from one of the preceding rows.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1525268
Posted Saturday, December 21, 2013 10:03 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 4,125, Visits: 3,425
Hugo Kornelis (12/21/2013)
Revenant (12/21/2013)
Hugo Kornelis (12/21/2013)
So how exactly does one "aggregate (...) those rows that had been processed BEFORE the current row" with LAG? . . .

http://technet.microsoft.com/en-us/library/hh231256.aspx

Not sure why you post that link. I know what LAG is, and what it does.
It does not answer the question on how to aggregate rows (watch the plural) with a function that is designed to return the value from one of the preceding rows.

Gotcha now. I will try to come up with an example of my own but it will have to wait until tomorrow - my pre-Christmas Saturday is rather busy.
Post #1525275
Posted Monday, December 23, 2013 4:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 11:05 AM
Points: 69, Visits: 21
Not crazy about this question as it is misleading. LEAD / LAG are not generally used to retrieve "all rows before this one" without doing some additional work to the query.

Really the purpose of LEAD / LAG is to provide an easy way to access 1 forward / 1 back of the current resultset without the need for a self join.

Lead: http://msdn.microsoft.com/en-us/library/hh213125.aspx
Lag: http://msdn.microsoft.com/en-us/library/hh231256.aspx

They don't really make any mention of a full result set being returned as a product of this analytic function alone.
Post #1525655
Posted Tuesday, December 24, 2013 11:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:49 AM
Points: 1,848, Visits: 587
Nice and easy one..
Thanks Steve.
Post #1525800
Posted Tuesday, December 24, 2013 6:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:24 AM
Points: 7,740, Visits: 9,488
Steve Jones - SSC Editor (12/20/2013)
You would use unbounded preceding for all the rows. The title did say all the rows, but the question did not.

Steve, that's special pleading. LAG references a single row. the question says "those rows ...", not "a row ...". It also talks about an aggregate, and it's a bit odd to think of an aggregate applying to a single row. Can you give a sensible example of how LAG can be used to cause an aggregate to apply to rows (as opposed to a row)?


Tom
Post #1525832
Posted Wednesday, December 25, 2013 7:09 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 20, 2014 6:00 AM
Points: 588, Visits: 276
Good question. Thanks. I see some good conversation stemming from this question.
Post #1525890
Posted Thursday, December 26, 2013 10:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
Easy one, thanks.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1526008
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse