Join egghead, unlock knowledge.

Want more egghead?

This lesson is for members. Join us? Get access to all 3,000+ tutorials + a community with expert developers around the world.

Unlock This Lesson
Become a member
to unlock all features

Level Up!

Access all courses & lessons on egghead today and lock-in your price for life.


    Learn What A Window Function Can Do

    Brett CassetteBrett Cassette

    In this lesson we’ll see how we can partition movies into the years they were released, and find the top 5 rated titles each year. The power of the window function is to partition data into groups and answer questions about those groups.



    Become a Member to view code

    You must be a Member to view code

    Access all courses and lessons, track your progress, gain confidence and expertise.

    Become a Member
    and unlock code for this lesson




    We'll start learning window functions by taking a look at row number. Row number is a really simple window function. We'll take a look at partitioning by year. This says that the window will be each year. 1893 is a window, all these for 1894 are a window, all of these for 1895 are a window.

    We can see that we're just numbering each row here, but we haven't defined an order for these to be in. This numbering is arbitrary. Instead, we'll say, "Order by rating, descending." This will be our ranking. The lowest rated ones will be at the bottom, the highest rated ones will be at the top.

    The highest rated movie in 1894 gets first place, the lowest rated movie in 1894 gets ninth place. These are based on the size of the window. In 1895 there are only three movies, last place is actually three.

    What's really cool is that now we can perform arbitrary selects over these. We can say, "Select star from this sub-query," and we'll give it a name, "Ratings," where ratings.ranking equals one.

    Now, we have the best-rated movies of each year, 1893, 1894, etc. etc. We could say, "Less than or equal to five." Now, we have the top five movies each year. We probably want to make sure we order by year, and ranking, ascending, just to be specific.