Re: Numbering rows by date - Mailing list pgsql-general

In article <ft074l$46m$1@news.hub.org>,
"Andrus" <kobruleht2@hot.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;