Numbering a records

From: "NTPT" <ntpt(at)centrum(dot)cz>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Numbering a records
Date: 2004-02-18 12:56:06
Message-ID: 003701c3f61e$92c76a20$d300a8c0@webpropag.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have this table

content (id int8,owner int8,position int8,timestamp int8,description text,batch int8)

Table is inserted/deleted frequently, 'id' is almoust random.

I insert to the table following set of rows :

12345, 1000,1,timestamp,blabla,0
12349, 1000,2,timestamp,blabla,0
12355, 1001,1,timestamp,blabla,0
12389, 1000,3,timestamp,blabla,0
etc.. There is a many of these records.

Now I need to od some select like this

select * from content where owner='1000' order by timestamp with some limits, offsets etc. It is OK, no problem.

Other select, like to need select a record of user 1000 WHERE position >5 AND position <150 is OK,

But now, some records are inserted, some deleted, some have the timestamp column updated, so column 'position' is not sequential anymore. I need to create some UPDATE ..... where owner='id of the owner' ORDER by timestamp, that will
recalculate column 'position' to contain actual position inside a timestamp ordered table ? (ie. colum position contain an actual order of records that is owned by 'owner' ordered by timestamp ).Please note that usage of plain LIMIT/OFFSET is not what I need.

in close relation to this, I have another problem. I NEED to assign bath number to records from this example. ie in the table content, where owner='id of the owner' ordered by timestamp, set of first 500 record should have the same 'bath' number '1', set of 2nd 500 records should have its batch number '2' etc...

Is it possible and how it can be done ?

PS: Execuse my bad english.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jan Wieck 2004-02-18 13:25:42 Re: PostgreSQL Indexing versus MySQL
Previous Message Richard Huxton 2004-02-18 12:46:37 Re: Differences between postgres and mysql