More Row Comparisons
Question:
Each cat would like to see the next heaviest cat's weight when grouped by breed. If there is no heavier cat print 'fattest cat'
Print a list of cats, their weights and either the next heaviest cat's weight or 'fattest cat'
Return: name, weight, breed, next_heaviest
Order by: weight
Show Table
Cats:
| name | varchar |
| breed | varchar |
| weight | float |
| color | varchar |
| age | int |
Show Desired Output
Desired output:
| name | weight | breed | next_heaviest |
| Tigger | 3.80 | British Shorthair | 4.8 |
| Molly | 4.20 | Persian | 4.5 |
| Ashes | 4.50 | Persian | 5 |
| Charlie | 4.80 | British Shorthair | 4.9 |
| Smudge | 4.90 | British Shorthair | fattest cat |
| Felix | 5.00 | Persian | fattest cat |
| Puss | 5.10 | Maine Coon | 5.4 |
| Millie | 5.40 | Maine Coon | 5.7 |
| Alfie | 5.50 | Siamese | 6.1 |
| Misty | 5.70 | Maine Coon | 6.1 |
| Oscar | 6.10 | Siamese | fattest cat |
| Smokey | 6.10 | Maine Coon | fattest cat |
Query Window:
Correct output but can you use 'lead'?
Show Answer
Answer:
select name, weight, breed,
coalesce(cast(lead(weight, 1) over (partition by breed order by weight) as varchar), 'fattest cat') as next_heaviest
from cats order by weight