From: | Harald Fuchs <hari(dot)fuchs(at)googlemail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Numbering rows by date |
Date: | 2008-04-06 09:06:14 |
Message-ID: | pu1w5j5p4p.fsf@srv.protecting.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
In article <ft074l$46m$1(at)news(dot)hub(dot)org>,
"Andrus" <kobruleht2(at)hot(dot)ee> writes:
> I have table
> create Document ( docdate date, docorder integer )
> I need update docorder column with numbers 1,2 in docdate date order
> Something like
> i = 1;
> UPDATE Document SET docorder = i++
> ORDER BY docdate;
> How to do this is PostgreSQL 8.2 ?
I don't think you can avoid a temp table:
CREATE TEMP TABLE tmp (
docdate date,
docorder serial
);
INSERT INTO tmp (docdate)
SELECT docdate
FROM documents
ORDER BY docdate;
UPDATE documents d
SET docorder = t.docorder
FROM tmp t
WHERE d.docdate = t.docdate;
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2008-04-06 11:13:18 | Using tsearch2 in a Bayesian filter |
Previous Message | Pavel Stehule | 2008-04-06 08:07:24 | Re: pl/pgsql RECORD data type, how to access to the values |