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
