Pro lesson Intermediate
Latest wins: window functions
Keep the newest version of each row when updates arrive more than once.
10 min8 hands-on steps
In this lesson you will
- See why GROUP BY cannot pick the newest row
- Number the rows inside each group with ROW_NUMBER
- Keep the latest row per customer with QUALIFY and a tie-breaker
Words you'll meet
- Window function
- A function that looks at a group of rows but keeps every row, adding its answer as a new column.
- Partition
- One group of rows inside a window function, such as all the rows for one customer.
- Deduplicate
- Keep one row per key and drop the other copies.
- Tie-breaker
- An extra sort column that decides between rows that would otherwise tie.
The steps
Each step teaches one idea, then has you try it hands-on in your browser, and checks your work.
- 1Updates arrive more than once
- 2GROUP BY mixes rows
- 3Number the rows
- 4Start again for each customer
- 5Keep the newest
- 6QUALIFY, the shortcut
- 7Ties
- 8Challenge
This lesson is part of LearnDE Pro
$12 a month or $100 a year: every lesson, spaced-repetition flashcards, and Learn by teaching. Refund on request within 14 days.