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
