First of each Group
Question:
Cats are vain. Each cat would like to pretend it has the lowest weight for its color.
Print cat name, color and the minimum weight of cats with that color.
Return: name, color, lowest_weight_by_color
Order by: color, name
Show Table
Cats:
| name | varchar |
| breed | varchar |
| weight | float |
| color | varchar |
| age | int |
Show Desired Output
Desired output:
| name | color | weight_by_color |
| Ashes | Black | 4.20 |
| Charlie | Black | 4.20 |
| Molly | Black | 4.20 |
| Oscar | Black | 4.20 |
| Smudge | Black | 4.20 |
| Alfie | Brown | 5.50 |
| Misty | Brown | 5.50 |
| Smokey | Brown | 5.50 |
| Felix | Tortoiseshell | 3.80 |
| Millie | Tortoiseshell | 3.80 |
| Puss | Tortoiseshell | 3.80 |
| Tigger | Tortoiseshell | 3.80 |
Query Window:
Correct output but can you use 'first_valuenth_valuemin'?
×
Good work!
first_value() [and nth_value() & min()] allow us to select the first value of a subgroup next question
Show Answer
Answer:
select name, color,
first_value(weight) over (partition by color order by weight) as weight_by_color
from cats order by color, name