Date: Wed, 2 Nov 2011 17:51:23 +0100
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-sql@postgresql.org
Subject: Re: Number timestamped rows
Message-ID: <20111102165122.GA22934@tux>
References: <20111102163619.135780@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20111102163619.135780@gmx.net>
User-Agent: Mutt/1.5.18 (2008-05-17)

Jan Peters <petersjan@gmx.at> wrote:

> Dear all,
> maybe a stupid question, but: I have a table that is ordered like this:
> 
> user_id|timestamp|event
> 1      |0:1      |event_a
> 1      |0:2      |event_b
> 2      |0:1      |event_b
> 2      |0:3      |event_c
> 2      |0:4      |event_b
> 3      |0:1      |event_a
> 
> and I would like to number them according to their timestamps like this:
> 
> user_id|timestamp|event   |order
> 1      |0:1      |event_a |1
> 1      |0:2      |event_b |2
> 2      |0:1      |event_b |1
> 2      |0:3      |event_c |2
> 2      |0:4      |event_b |3
> 3      |0:1      |event_a |1
> 

untested:

select user_id, timestamp, event, row_number() over (partition by
user_id order by timestamp) as "order" from your_table


> How would I do this with an UPDATE statement (e.g.) in pgsql?

I think, you don't need an UPDATE, just a SELECT.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°