1 Sequence per Row i.e. each customer's first order starts at 1 - Mailing list pgsql-general

From Merrick
Subject 1 Sequence per Row i.e. each customer's first order starts at 1
Date
Msg-id b1972c6a0907011704i1cf707a1u11c439b09d1958a4@mail.gmail.com
Whole thread Raw
Responses Re: 1 Sequence per Row i.e. each customer's first order starts at 1
Re: 1 Sequence per Row i.e. each customer's first order starts at 1
List pgsql-general
I have been using postgresql for 8 years in web projects and ran into
a problem that I could not find a solution for in the archives or
through Google.

Here is a generalized example of what I want to happen. I have a
customers table, and an orders table. I would like for each customer
to have orders that start at 1 and move up sequentially. I realize
it's probably not efficient to create a new sequence for each
customer, so am looking for alternate ways to accomplish the same
thing. Below is an illustrated example of the outcome I would like. I
would also like similar functionality to a sequence so duplicate
order_id's are not generated. Please keep in mind that for what I am
developing, having each customer's orders start at 1 is more of a need
than a want.

Thank you.


Customers
customer_id     customer_name
----------------------------------------------------------------
1            acme corp
2            hacker corp
3            dixie corp


Orders
order_id    customer_id        timestamp
----------------------------------------------------------------
1            1            2009-07-01 14:42
1            2            2009-07-01 14:43
2            2            2009-07-01 14:44
1            3            2009-07-01 14:44
2            1            2009-07-01 14:44
3            1            2009-07-01 14:44

pgsql-general by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: datestyle no effect in 8.3.7
Next
From: John Cheng
Date:
Subject: Problem search on text arrays, using the overlaps (&&) operator