Special Case Grouping
Question:
The cats have decided the correct weight is the same as the 4th lightest cat. All cats shall have this weight. Except in a fit of jealous rage they decide to set the weight of the lightest three to 99.9
Print a list of cats, their weights and their imagined weight
Return: name, weight, imagined_weight
Order by: weight
Show Table
Cats:
| name | varchar |
| breed | varchar |
| weight | float |
| color | varchar |
| age | int |
Show Desired Output
Desired output:
| name | weight | imagined_weight |
| Tigger | 3.80 | 99.90 |
| Molly | 4.20 | 99.90 |
| Ashes | 4.50 | 99.90 |
| Charlie | 4.80 | 4.80 |
| Smudge | 4.90 | 4.80 |
| Felix | 5.00 | 4.80 |
| Puss | 5.10 | 4.80 |
| Millie | 5.40 | 4.80 |
| Alfie | 5.50 | 4.80 |
| Misty | 5.70 | 4.80 |
| Oscar | 6.10 | 4.80 |
| Smokey | 6.10 | 4.80 |
Query Window:
Correct output but can you use 'nth_value'?
Show Answer
Answer:
select name, weight,
coalesce(nth_value(weight, 4) over (order by weight), 99.9) as imagined_weight
from cats order by weight