Re: Numbering rows by date

Andrus wrote:
> 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 ?
>

ALTER TABLE DROP COLUMN docorder;
SELECT docdate FROM document ORDER BY docdate ASC;

Or, if you really need the numbering and can't do it in application code
... my first thought was to do this:

CREATE TEMP SEQUENCE seq_doc_number;
SELECT docdate, nextval('seq_doc_number') FROM document
ORDER BY docdate ASC;

But the ordering will occur afterwards so the sequence will be out of
order. I think you'd need a subquery, then:

CREATE TEMP SEQUENCE seq_doc_number;
SELECT docdate, nextval('seq_doc_number') AS docorder
FROM (SELECT docdate FROM document ORDER BY docdate ASC) dummy_alias;

b