My dataset looks like this:
2 groups (G1 and G2) of 4 items each
Group G1 contains
id value
A1 9
A2 7
B1 3
B2 2
Group G2 contains
id value
A3 9
A4 7
B2 1
B3 0
I would like a query that returns the item(s) with the lowest value
with the restriction that if taht item is in multiple groups it must
have teh lowest value in each group (or be tied for the lowest)
B2 in group 1 has a value of 2 (the lowest in that group), but its
also in group 2 and has a value (1) that is not the lowest in that
group (even though its lower than in group 1)
The above is a simplification, as there will be several groups, each
with up to 10 items.
There are perhaps 10 groups and 50 items max.
How can I write this query other than through a set of while
statements and temp tables that are iteratively built up?
Thank you for any ideas
2 groups (G1 and G2) of 4 items each
Group G1 contains
id value
A1 9
A2 7
B1 3
B2 2
Group G2 contains
id value
A3 9
A4 7
B2 1
B3 0
I would like a query that returns the item(s) with the lowest value
with the restriction that if taht item is in multiple groups it must
have teh lowest value in each group (or be tied for the lowest)
B2 in group 1 has a value of 2 (the lowest in that group), but its
also in group 2 and has a value (1) that is not the lowest in that
group (even though its lower than in group 1)
The above is a simplification, as there will be several groups, each
with up to 10 items.
There are perhaps 10 groups and 50 items max.
How can I write this query other than through a set of while
statements and temp tables that are iteratively built up?
Thank you for any ideas
Comment