In response to A. Kretschmer : > In response to Zsolt : > > > > This is our first project using PostgerSQL, where I have a problem I cant solve > > on a neat way (I assume PGSQL should provide a nice solution...). > > > > So we have an old xBase based program we are trying to port to PostgreSQL while > > we should keep the original data structure especially the ID fields must be > > kept as this IDs are already used in other systems. > > > > The problem is with two table, one is storing the data of houses the other the > > data of tenants in a given houses. > > > > > > > > For a given house I would like to start the numbering of tenants from 1. Each > > house could have tenant_ID=1, obviously in this case the house_ID will differ. > > The combination of tenant_ID and house_ID will be the unique identifier of each > > tenant. > > Do you have PostgreSQL 8.4? > > If yes, you can use CTE-functions for that. row_number(). > > Unfortunately, at the moment i haven't access to my database to create > an example, maybe later. Okay, i'm back and here my example: test=*# select * from house; id | name ----+-------- 1 | house1 2 | house2 (2 rows) test=*# select * from tenant; id | house_id | name ----+----------+--------- 1 | 1 | tenant1 2 | 1 | tenant2 3 | 1 | tenant3 4 | 2 | tenant4 5 | 2 | tenant5 6 | 2 | tenant6 7 | 2 | tenant7 (7 rows) test=*# select id, house_id, row_number() over (partition by house_id) as tenant_house, name from tenant order by id, house_id; id | house_id | tenant_house | name ----+----------+--------------+--------- 1 | 1 | 1 | tenant1 2 | 1 | 2 | tenant2 3 | 1 | 3 | tenant3 4 | 2 | 1 | tenant4 5 | 2 | 2 | tenant5 6 | 2 | 3 | tenant6 7 | 2 | 4 | tenant7 (7 rows) Hope that helps, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
