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:

> 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 Finney
Received on Mon Nov 30 2009 - 19:08:58 CST

Original text of this message