"Here's the setup:
CREATE TABLE One (OneID INTEGER, OneDesc VARCHAR(20))
CREATE TABLE Many (ManyID INTEGER, ManyDesc VARCHAR(20))
CREATE TABLE Usage (OneID INTEGER, ManyID INTEGER)
INSERT Many(ManyID, ManyDesc) VALUES (1, 'First')
INSERT Many(ManyID, ManyDesc) VALUES (2, 'Second')
INSERT Many(ManyID, ManyDesc) VALUES (3, 'Third')
INSERT Many(ManyID, ManyDesc) VALUES (4, 'Fourth')
INSERT One(OneID, OneDesc) VALUES (1, 'First only')
INSERT One(OneID, OneDesc) VALUES (2, 'Second only')
INSERT One(OneID, OneDesc) VALUES (3, 'Third only')
INSERT One(OneID, OneDesc) VALUES (4, 'Fourth only')
INSERT One(OneID, OneDesc) VALUES (5, 'First & Second')
INSERT One(OneID, OneDesc) VALUES (6, 'First & Third')
INSERT One(OneID, OneDesc) VALUES (7, 'First & Fourth')
INSERT One(OneID, OneDesc) VALUES (8, 'Second & Third')
INSERT One(OneID, OneDesc) VALUES (9, 'Second & Fourth')
INSERT One(OneID, OneDesc) VALUES (10, 'First, Second, & Third')
INSERT Usage(OneID, ManyID) VALUES (1, 1)
INSERT Usage(OneID, ManyID) VALUES (2, 2)
INSERT Usage(OneID, ManyID) VALUES (3, 3)
INSERT Usage(OneID, ManyID) VALUES (4, 4)
INSERT Usage(OneID, ManyID) VALUES (5, 1)
INSERT Usage(OneID, ManyID) VALUES (5, 2)
INSERT Usage(OneID, ManyID) VALUES (6, 1)
INSERT Usage(OneID, ManyID) VALUES (6, 3)
INSERT Usage(OneID, ManyID) VALUES (7, 1)
INSERT Usage(OneID, ManyID) VALUES (7, 4)
INSERT Usage(OneID, ManyID) VALUES (8, 2)
INSERT Usage(OneID, ManyID) VALUES (8, 3)
INSERT Usage(OneID, ManyID) VALUES (9, 2)
INSERT Usage(OneID, ManyID) VALUES (9, 4)
INSERT Usage(OneID, ManyID) VALUES (10,1)
INSERT Usage(OneID, ManyID) VALUES (10,2)
INSERT Usage(OneID, ManyID) VALUES (10,3)
Okay, whew. Now, the goal is to write a query that will return ten rows, one for each item in the table One, and two columns. The first column should be the OneDesc, and the second should be the ManyDesc for the *maximum* ManyID in the usage table. Here's what I'm looking for:
OneDesc ManyDesc
------------------------------ ------------------------------
First Only First
Second Only Second
Third Only Third
Fourth Only Fourth
First & Second Second
First & Third Third
First & Fourth Fourth
Second & Third Third
Second & Fourth Fourth
First, Second, and Third Third
I was able to generate this using the following query:
SELECT
o.OneDesc,
m.ManyDesc
FROM
One o
INNER JOIN Usage u ON
u.OneID = o.OneID
INNER JOIN Many m ON
m.ManyID = u.ManyID AND
m.ManyID = (SELECT MAX(u1.ManyID) FROM Usage u1 WHERE u1.OneID = o.OneID)
ORDER BY
o.OneID
This works. But that subquery quite frankly gives me the screaming heebie-jeebies, if you know what I mean. Is there some slick way to do this using GROUP BY and HAVING, avoiding the subquery?"
Finding maximum values in one-to-many usage tables 
Posted - 2000-11-30 : 11:02:56