From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-sql@postgresql.org
Subject: Re: Number timestamped rows
Date: Wed, 2 Nov 2011 17:51:23 +0100
Message-ID: <20111102165122.GA22934@tux> (raw)
In-Reply-To: <20111102163619.135780@gmx.net>
References: <20111102163619.135780@gmx.net>
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°
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: akretschmer@spamfence.net
Subject: Re: Number timestamped rows
In-Reply-To: <20111102165122.GA22934@tux>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox