Postgres Pro
Downloads
Home   >   mailing lists

Thread: Numbering rows by date

Numbering rows by date

From
"Andrus"
Date:
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 ?

Andrus.

Re: Numbering rows by date

From
brian
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

Re: Numbering rows by date

From
Harald Fuchs
Date:
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;

Re: Numbering rows by date

From
Volkan YAZICI
Date:
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.

Re: Numbering rows by date

From
"Andrus"
Date:
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.