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

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;

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.

Volkan,

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;

thank you. Excellent.

Andrus.