Thread: Add serial in specific order?

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...

Thanks for any advice.

Poul Jensen

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