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

On Wed, 2 Apr 2008, "Andrus" <kobruleht2@hot.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.