How to do this in Postgres

Hi there,

I tried all I could think of with the following problem, perhaps
someone has another idea.

I have a table where for each id there may (and often are) multiple
rows with some kind of priority.
  create table data ( id1 int4, id2 int4, <<lots of data>>, prio int4 );
The minimal priority is not guaranteed to be 1. There are 200k
different ids with up to 10 entries, summing up to 400k rows.

Not I want to do something like this:

    select * from data where <<prio is minimal per id pair>>.

First attempt (deleting non minimal)
------------------------------------

    select a.id1, a.id2, a.prio
    into bugos
    from a data, b data
    where a.prio > b.prio and a.id1 = b.id1 and a.id2 = b.id2;

    delete from data
    where id1 = bogus.id1 and id2 = bogus.id2 and prio = bogus.prio;

The join does not seem to complete. I am not sure whether I should
have waited longer, but after 4h without significant disk access I
do not think that this thing will ever return. Indexing didn't help.

Second attempt (stored procedures)
----------------------------------

    create function GetData( int4, int4 )
    returns data
    as 'select *
        from data
        where id1 = $1 and id2 = $2
        order by prio
        limit 1'
    language 'sql';

    select GetData(id1,id2) from <<table with unique ids>>;

limit in functions is not yet implemented in postgres (6.5.2)

Third attempt (use perl on dumped table)
----------------------------------------

I don't want to :-)

Regards,
    Holger Klawitter
--
Holger Klawitter                                     +49 (0)251 484 0637
holger@klawitter.de                             http://www.klawitter.de/