From: | Sarah Dougherty <sdougherty(at)desc(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Using generate_series to create a unique ID in a query? |
Date: | 2007-11-12 23:11:50 |
Message-ID: | 4738DDB6.2090900@desc.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I am trying to create a view that will contain a generated sequence
(unique ID), and am running into problems doing so.
For some context, I am trying to create a report that provides a list of
client charges and payments and a "running balance" after each
transaction. Because we often have multiple charges and/or payments on
the same day, we can't use the transaction date to calculate this
balance. Instead, I want to calculate our running balance by assigning
a transaction ID to each transaction a d then having the query sum up
transaction amounts for all transactions with an equal or lower ID.
I can use generate_series to produce a set of IDs, but can't get it to
join properly to the rest of my query. For example, if I had 10 rows in
my query, I would get a series of 1 to 10, but would then get 100 rows
(10x10) in my result. Ultimately the results of this query are going to
be used as a view, so I'd like to avoid creating a temp table, sequence,
etc. Does anyone know how to use generate_series in this manner, or know
of some other way I can go about this? Thanks in advance!
To recap with an example, the query below works fine, but how do I add a
series to it?
SELECT * FROM (
SELECT
client_id,
effective_date AS transaction_date,
amount AS charge_amount,
0 AS payment_amount
FROM charge
UNION
SELECT
client_id,
payment_date AS transaction_date,
0 as charge_amount,
amount AS payment_amount
FROM payment
) AS tmp
ORDER BY
transaction_date,
charge_amount<>0 /* order charges before payments */
Thanks,
Sarah Dougherty
Attachment | Content-Type | Size |
---|---|---|
sdougherty.vcf | text/x-vcard | 216 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Manes | 2007-11-12 23:39:10 | FreeBSD portupgrade of 8.1 -> 8.2 |
Previous Message | Scott Marlowe | 2007-11-12 23:09:51 | Re: update record with two-column primary key |