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


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 937, Visits: 390
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 @ 10:59 AM
Points: 5,801, Visits: 8,018
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: Today @ 12:35 PM
Points: 4,247, Visits: 3,333
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 @ 10:59 AM
Points: 5,801, Visits: 8,018
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: Today @ 12:35 PM
Points: 4,247, Visits: 3,333
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 8:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:36 PM
Points: 2,111, Visits: 122
LAG is new to me. I'm not sure when or how I might use it for any of my daily SQL work. I need to read up more about it.
Post #1525573
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: Yesterday @ 3:32 PM
Points: 62, Visits: 20
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: Yesterday @ 7:14 AM
Points: 1,682, Visits: 551
Nice and easy one..
Thanks Steve.
Post #1525800
Posted Tuesday, December 24, 2013 6:51 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 8,295, Visits: 8,747
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
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse