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 Schema
Cats:
| name | varchar |
| breed | varchar |
| weight | float |
| color | varchar |
| age | int |
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
Desired output:
| name | color | weight_by_color |
| Ashes | Black | 4.2 |
| Charlie | Black | 4.2 |
| Molly | Black | 4.2 |
| Oscar | Black | 4.2 |
| Smudge | Black | 4.2 |
| Alfie | Brown | 5.5 |
| Misty | Brown | 5.5 |
| Smokey | Brown | 5.5 |
| Felix | Tortoiseshell | 3.8 |
| Millie | Tortoiseshell | 3.8 |
| Puss | Tortoiseshell | 3.8 |
| Tigger | Tortoiseshell | 3.8 |
select name, color, first_value(weight) over (partition by color order by weight) as weight_by_color from cats order by color, name