SEO

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

-- If you are using SQL Server 2005, you can do something like this

CREATE TABLE #Groups(grp CHAR(2), id CHAR(2), value INT)
INSERT INTO #Groups(grp, id, value )
SELECT 'G1','A1', 9 UNION ALL
SELECT 'G1','A2', 7 UNION ALL
SELECT 'G1','B1', 3 UNION ALL
SELECT 'G1','B2', 2 UNION ALL
SELECT 'G2','A3', 9 UNION ALL
SELECT 'G2','A4', 7 UNION ALL
SELECT 'G2','B2', 1 UNION ALL
SELECT 'G2','B3', 0 ;

WITH CTE AS (
SELECT grp, id, value,
ROW_NUMBER() OVER(PARTITION BY grp ORDER BY value) AS rn
FROM #Groups)
SELECT ID
FROM CTE
GROUP BY ID
HAVING COUNT(*)=SUM(rn)

To handle "tied for the lowest" use RANK instead of ROW_NUMBER

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Sample data is also a good idea,
along with clear specifications. It is very hard to debug code when
you do not let us see it.

If you want to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

>> 2 groups (Foobarand G2) of 4 items each <<

Do you know that group has a special meaning in SQL? And that
"dataset" is not an SQL term at all? Here is my guess, using a proper
schema where all items of one kind are in one table.

CREATE TABLE Foobar
(grp_id CHAR(2) NOT NULL,
item_id CHAR(2) NOT NULL,
PRIMARY KEY (grp_id, item_id),
foobar_value INTEGER NOT NULL);

DELETE FROM Foobar;
INSERT INTO Foobar VALUES ('g1', 'A1', 9);
INSERT INTO Foobar VALUES ('g1', 'A2', 7);
INSERT INTO Foobar VALUES ('g1', 'B1', 3);
INSERT INTO Foobar VALUES ('g1', 'B2', 2);

INSERT INTO Foobar VALUES ('g2', 'A3', 9);
INSERT INTO Foobar VALUES ('g2', 'A4', 7);
INSERT INTO Foobar VALUES ('g2', 'B2', 1);
INSERT INTO Foobar VALUES ('g2', 'B3', 10);

INSERT INTO Foobar VALUES ('g3', 'A5', 10);
INSERT INTO Foobar VALUES ('g3', 'A6', 8);
INSERT INTO Foobar VALUES ('g3', 'B2', 0);
INSERT INTO Foobar VALUES ('g3', 'B3', 0);

SELECT * FROM Foobar;

Find an item, if it exists, which is common to all (n) groups and
which is also the minimum value in all of the groups:

SELECT item_id
FROM (SELECT item_id,
RANK () OVER (PARTITION BY item_id
ORDER BY foobar_value, item_id ASC) AS
first_place_cnt
FROM Foobar) AS X(item_id, place)
WHERE first_place_cnt = (SELECT COUNT(DISTINCT grp_id) FROM Foobar);