3 ms·
I've used a few of these and it's great to practice using more! Q7 includes output: Misty 5.7 0.2 Oscar 6.1 0.4 Smokey 6.1 0.0 How would one sh
by karmakaze 9y ago
I've used a few of these and it's great to practice using more!
Q7 includes output:
Misty 5.7 0.2
Oscar 6.1 0.4
Smokey 6.1 0.0
How would one show 0.4 for both Oscar and Smokey?
http://www.windowfunctions.com/questions/7 http://www.windowfunctions.com/questions/7
- dancek 9y agoI'm not sure how you'd do this with windowing functions, but at least you can use common table expressions (WITH) to achieve what you're asking for. The site is down right now, so I had to test using my own db instance. Column and table names might not match. WITH distinct_weights AS ( SELECT DISTINCT weight FROM cats), weights AS ( SELECT weight, lag(weight) OVER ( ORDER BY weight ) AS lag FROM distinct_weights) SELECT cats.name, cats.weight, cats.weight - weights.lag FROM cats, weights WHERE cats.weight = weights.weight ORDER BY weight; yields Any 5.5 null Misty 5.7 0.2 Oscar 6.1 0.4 Smokey 6.1 0.4
- karmakaze 9y agoI see, it's joining with the lagged weight. Same without CTE: SELECT c.name, c.weight, c.weight - lw.lower_weight AS weight_to_lose FROM cats c JOIN (SELECT weight, lag(weight, 1, weight) over (order by weight) lower_weight FROM cats GROUP BY weight ORDER BY weight) lw ON c.weight = lw.weight ORDER BY weight