change "many-to-many" to "one-to-many"

Question

I have following table and data:

create table Foo
(
    id int not null,
    hid int not null,
    value int not null
)

insert into Foo(id, hid, value) values(1,1,1) -- use this as 1 < 3
insert into Foo(id, hid, value) values(1,2,3)

insert into Foo(id, hid, value) values(2,3,3) -- use this as 3 < 5
insert into Foo(id, hid, value) values(2,4,5)

insert into Foo(id, hid, value) values(3,2,2) -- use this or next one as value are the same
insert into Foo(id, hid, value) values(3,3,2)

Currently the "id" and "hid" has many-to-many association, what I want to achieve is to make the "hid" as "one" instead of "many", the rule is to use the minimum "value" in the table, see comment in above sql code.

Is this possible use some query to achieve this instead of a cursor?

Thanks!

Answer 1

Not sure if you are looking for a query, or instructions on how to modify your schema, but here is a query:

select id, min(hid) as hid, min(value) as value
from Foo
group by id

Answer 2

SQL 2005:

WITH X AS ( SELECT id, min(value) as minval from Foo group by id )
SELECT * FROM 
(
    SELECT Foo.*, RANK() OVER ( PARTITION by Foo.id order by Foo.hid, Foo.value ) as Rank
    FROM Foo JOIN X on Foo.id = X.id and Foo.value = X.minval
) tmp
WHERE Rank = 1


id          hid         value       Rank                 
----------- ----------- ----------- -------------------- 
1           1           1           1
2           3           3           1
3           2           2           1

The first line (WITH clause) gets a set of ids with the min value (my arbitrary choice). The RANK is used to eliminate duplicates - there may be a better way.

With MySql or SQL 2000 I guess you could do this with a complicated set of subqueries.