Re: Sql request
From: Ben Finney <bignose+hates-spam_at_benfinney.id.au>
Date: Tue, 01 Dec 2009 12:08:58 +1100
Message-ID: <87hbsbo5zp.fsf_at_benfinney.id.au>
Thomas Kellerer <OTPXDAJCSJVU_at_spammotel.com> writes:
display_code | order_amount | delivery_sequence | delivery_amount
Date: Tue, 01 Dec 2009 12:08:58 +1100
Message-ID: <87hbsbo5zp.fsf_at_benfinney.id.au>
Thomas Kellerer <OTPXDAJCSJVU_at_spammotel.com> writes:
> But you have a good point about the stock_order table not being
> necessary:
[…]
> Runs on PostgreSQL and DB2 just as well.
Interesting. But you've changed it from a relational to a non-relational result (some tuples now contain nulls). It also had some other errors (referencing names that weren't defined).
I'm new to windowing operations (thank you for the motivation to learn them), but I think this is an improvement of both the above points, plus a bit clearer for me to read:
SELECT
CASE WHEN ( LAG(order_code) OVER order_sequence ) = order_code THEN '' ELSE order_code END AS display_code, CASE WHEN ( LAG(order_code) OVER order_sequence ) = order_code THEN '' ELSE CAST ( SUM(delivery_amount) OVER (PARTITION BY order_code) AS VARCHAR) END AS order_amount, delivery_sequence, delivery_amount FROM ( SELECT order_code, sequence AS delivery_sequence, amount AS delivery_amount FROM stock_delivery ) AS d WINDOW order_sequence AS ( PARTITION BY order_code ORDER BY delivery_sequence ASC)ORDER BY order_code ASC, delivery_sequence ASC;
PostgreSQL 8.4 gives me:
display_code | order_amount | delivery_sequence | delivery_amount
--------------+--------------+-------------------+----------------- A | 100.00 | 1 | 50.00 | | 2 | 30.00 | | 3 | 20.00 B | 200.00 | 1 | 200.00 C | 120.00 | 1 | 70.00 | | 2 | 50.00(6 rows)
-- \ “Pinky, are you pondering what I'm pondering?” “Uh, I think so, | `\ Brain, but I get all clammy inside the tent.” —_Pinky and The | _o__) Brain_ | Ben FinneyReceived on Mon Nov 30 2009 - 19:08:58 CST