Analytic Partitions, Ordering, and Windows

Abstract

A wise man once said in a conference presentation that if you put SQL on your resume and do not know analytic functions, you are lying. It would be similar to stating you know Windows and have never worked with a newer windows version than Windows 95. There are so many cases where analytic functions either are necessary to create a SQL solution at all or at the very least make the SQL much more performant than not using them. The fantastic bit about analytic functions is that you can retrieve or reference values across rows – you are not restricted to values in the row itself when doing calculations. You can use different subclauses of analytic functions in different combinations to achieve this. The basics of these subclauses, and how they work together, are shown in this chapter.