3 ms·
I'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 i
by dancek 9y ago
I'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