Re: Numbering rows by date

On Wed, 2 Apr 2008, "Andrus" <kobruleht2(at)hot(dot)ee> writes:
> 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;

CREATE SEQUENCE document_docorder_seq START 1;

UPDATE document
SET docorder = T.docorder
FROM (SELECT nextval('document_docorder_seq') AS docorder, docdate
FROM document
ORDER BY docdate) AS T
WHERE document.docdate = T.docdate;

DROP SEQUENCE document_docorder_seq;

Regards.