Re: Add serial in specific order?

On Sep 26, 2006, at 21:22 , Poul Jensen wrote:

> I have ~25 columns in my database and need to order the rows by all
> columns to do queries like:
>
> SELECT a FROM table ORDER BY a, b, c, ...., z;
>
> I suspect it would be highly ineffective to order by all columns
> for every query! Hence I'd like to do the ordering only once and
> add a serial to the database specifying the order so all subsequent
> queries can be done like:
>
> SELECT b FROM table ORDER BY added_serial;
>
> The optimal ordering is found from analysis of the database and is
> not known at build time. Is it possible to add the serial without
> having to rebuild the database? It has millions of rows, by the way...

I think what I'd do is create another table that just stores the
order information and references the first table. For example, given
table foo:

create table foo
(
    foo_id integer primary key
    , foo_name text not null unique
    , foo_data text not null
);

Then create your ordering table, ordered_foo.

create table ordered_foo
(
    foo_id integer primary key
        references foo (foo_id)
        on update cascade on delete cascade
    , foo_ordering serial not null unique
);

To fill ordered_foo, just do:

insert into ordered_foo(foo_id)
select foo_id
from foo
order by foo_data;

Now you just have a join which will give you the order for you:

select *
from foo
natural join ordered_foo
order by foo_ordering;

You could even throw the join in a view for convenience:

create view ordered_foo_view as
select *
from foo
natural join ordered_foo;

Then you just:

select *
from ordered_foo_view
order by foo_ordering;

To update the ordering, just truncate ordered_foo and fill it again.

Anyway, that's one way to do it.

Hope this helps.

Michael Glaesemann
grzm seespotcode net