Re: Numbering rows by date

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;