Try this :
select *
from data
group by id1,id2
having prio=min(prio)
I'm not sure it works with Postgres, using Sybase this would
returns a lot of duplicates since the group by mechanism is
a bit strange.
If it does't, this should work :
select id1,id2
into bogus
from data
group by id1,id2
having prio=min(prio)
select data.id1,data.id2,<<lots of data>>
from data,bogus
where data.id1=bogus.id1 and data.id2=bogus.id2
drop table bogus
Alain
--- Holger Klawitter <holger@klawitter.de> wrote:
> 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>>.
