Module 5
Filtering and sorting rows
Filter by value, combine conditions, sort on several keys and keep top or bottom rows.
You'll be able to
- Write repeatable row filters
- Handle null in conditions
- Sort and rank rows
Lessons
- 1.Filter rows by valueWrite filters that describe the rows you want, so they keep working when the data changes.
- 2.Sort rows on more than one columnSort on several keys in one step and know when order is meaningful for the output.
- 3.Keep the top N rows after sortingRank rows by a calculated value and keep the top N in a refresh-safe way.
- 4.Remove blank rows and keep an explicit row orderDrop empty rows safely and keep a reliable record of source order.
Take the filtering and sorting rows quiz (8 questions)
Exercises
- beginner~5 minFree
Keep only completed orders (kite shop)
Filter out cancelled and pending orders so only completed sales remain.
- beginner~5 minPro
Keep only completed orders (bakery)
Filter out cancelled and pending orders so only completed sales remain.
- beginner~5 minPro
Keep only completed orders (bike rental)
Filter out cancelled and pending orders so only completed sales remain.
- beginner~7 minFree
Find bulk orders in a price band (kite shop)
Keep orders of at least 4 units priced under 20, ignoring rows with no quantity.
- beginner~7 minPro
Find bulk orders in a price band (bakery)
Keep orders of at least 4 units priced under 20, ignoring rows with no quantity.
- beginner~7 minPro
Find bulk orders in a price band (bike rental)
Keep orders of at least 4 units priced under 20, ignoring rows with no quantity.
- beginner~6 minFree
Sort orders by region, then by price (kite shop)
Produce a list sorted by Region A→Z and, within each region, by UnitPrice from highest to lowest.
- beginner~6 minPro
Sort orders by region, then by price (bakery)
Produce a list sorted by Region A→Z and, within each region, by UnitPrice from highest to lowest.
- beginner~6 minPro
Sort orders by region, then by price (bike rental)
Produce a list sorted by Region A→Z and, within each region, by UnitPrice from highest to lowest.
- intermediate~8 minFree
Find the three largest orders (kite shop)
Calculate each order's value and keep the three biggest, largest first.
- intermediate~8 minPro
Find the three largest orders (bakery)
Calculate each order's value and keep the three biggest, largest first.
- intermediate~8 minPro
Find the three largest orders (bike rental)
Calculate each order's value and keep the three biggest, largest first.
- intermediate~7 minFree
Keep orders for a list of products (kite shop)
Keep only orders whose Product is in the promotion list held in a separate PromoProducts table.
- intermediate~7 minPro
Keep orders for a list of products (bakery)
Keep only orders whose Product is in the promotion list held in a separate PromoProducts table.
- intermediate~7 minPro
Keep orders for a list of products (bike rental)
Keep only orders whose Product is in the promotion list held in a separate PromoProducts table.
- beginner~6 minFree
Find stock held in warehouse B and running low (hardware)
Keep rows in warehouse WH-B where OnHand is below the reorder level.
- beginner~6 minPro
Find stock held in warehouse B and running low (pharmacy)
Keep rows in warehouse WH-B where OnHand is below the reorder level.
- beginner~6 minPro
Find stock held in warehouse B and running low (cafe)
Keep rows in warehouse WH-B where OnHand is below the reorder level.
- intermediate~7 minFree
Remove completely blank rows, keep partly empty ones (clinic)
A sign-in sheet has spacer rows where every cell is empty. Remove those, but keep real rows that just lack a phone or city.
- intermediate~7 minPro
Remove completely blank rows, keep partly empty ones (workshop)
A sign-in sheet has spacer rows where every cell is empty. Remove those, but keep real rows that just lack a phone or city.
- intermediate~8 minFree
Filter orders to a date window from a parameter table (kite shop)
A one-row Settings table holds StartDate and EndDate. Keep orders inside that window, both ends included.
- intermediate~8 minPro
Filter orders to a date window from a parameter table (bakery)
A one-row Settings table holds StartDate and EndDate. Keep orders inside that window, both ends included.
- intermediate~8 minPro
Filter orders to a date window from a parameter table (bike rental)
A one-row Settings table holds StartDate and EndDate. Keep orders inside that window, both ends included.
- intermediate~8 minFree
Filter with and/or conditions in the right grouping (kite shop)
Keep big orders from anywhere, plus non-cancelled orders from North or South. Parentheses matter.
- intermediate~8 minPro
Filter with and/or conditions in the right grouping (bakery)
Keep big orders from anywhere, plus non-cancelled orders from North or South. Parentheses matter.
- intermediate~8 minPro
Filter with and/or conditions in the right grouping (bike rental)
Keep big orders from anywhere, plus non-cancelled orders from North or South. Parentheses matter.
- intermediate~8 minFree
Sort tickets by business priority, not alphabetically (telecom)
Sort Urgent, High, Medium, Low (in that order), then oldest first within each priority.
- intermediate~8 minPro
Sort tickets by business priority, not alphabetically (saas)
Sort Urgent, High, Medium, Low (in that order), then oldest first within each priority.