All the rows before this one

Question

Post reply

All the rows before this one

Steve Jones - SSC Editor

SSC Guru

Points: 736494
More actions
December 19, 2013 at 10:10 pm

#301488

Comments posted to this topic are about the item All the rows before this one

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 1

Nice question - thanks steve

Did a bit of digging...as another explanation

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

Hope this helps...

Ford Fairlane
Rock and Roll Detective

paul s-306273 SSChampion Points: 10730 More actions · Answer 2

paul s-306273

SSChampion

Points: 10730

December 20, 2013 at 1:36 am

#1675066

Hey Steve - nice easy one to finish the week.

Cheers.

sestell1 SSChampion Points: 10230 More actions · Answer 3

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/

steve.jacobs SSCommitted Points: 1830 More actions · Answer 4

steve.jacobs

SSCommitted

Points: 1830

December 20, 2013 at 8:40 am

#1675179

Steve, very nice question. Thanks

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 5

You would use unbounded preceding for all the rows. The title did say all the rows, but the question did not.

sneumersky SSCertifiable Points: 7667 More actions · Answer 6

I am so glad SQL Server finally caught up to Oracle on making itself more ANSI-99 compliant in the area of "row based window frames". I always theorized MS intentionally did this to get customers to try SSAS. This, of course, is a baseless theory of mine that I could never prove. Does anyone know the REAL answer as to why it took so long?

I would also love to one day see a Modenar* regarding performance diffferences between native window framing in SQL 2012 vs pre-2012, more imaginative (sorta) solutions. As The Kimball Group once said "just because your RDBMS is ANSI compliant does NOT mean the RDBMS performs those operations well"!

Thanks, Steve!

*A Modenar is a passionate teaching to the SQL Server community on set-based data processing and all the tools available to do so in a tireless effort to rid the world of intra-database RBAR.

Thomas Abraham SSChampion Points: 10761 More actions · Answer 7

Nice question and good feature. Makes rolling calcs easier.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Revenant SSC-Forever Points: 42467 More actions · Answer 8

Revenant

SSC-Forever

Points: 42467

December 20, 2013 at 9:54 am

#1675222

Nice, Steve, very nice - thanks!

BarbW SSCarpal Tunnel Points: 4759 More actions · Answer 9

That is a new piece of helpful information. Thanks! 😀

Hany Helmy SSChampion Points: 13921 More actions · Answer 10

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.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 11

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Revenant SSC-Forever Points: 42467 More actions · Answer 12

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 13

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Revenant SSC-Forever Points: 42467 More actions · Answer 14

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.