> I would like a query that returns the item(s) with the lowest value with
the restriction that if that item is in multiple groups it must have the
lowest value in each group (or be tied for the lowest) <<
this was already answered in a SQL SERVER newsgroup on March 2
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
CREATE TABLE Foobar
(grp_id CHAR(2) NOT NULL,
item_id CHAR(2) NOT NULL,
PRIMARY KEY (grp_id, item_id), -- assumption from lack of specs
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);
|
