[Hidden-tech] Mind Puzzle For All You SQL Gurus

[dschein at fsinnovations.net]

>I am trying to write a query on two tables, with a many to one relationship.
>Let us call the primary table A, and the foreign key table B.  For each
>record in table A, I would like 1 (and only 1) corisponding record from
>table B.  The 1 record should be the result of the top record from a sort on
>table B.

SQL dialect matters, but 1 pretty general solution (shown using SQLite):

sqlite> create table a (akey integer primary key, aval text);
sqlite> create table b (bkey integer primary key, fakey integer, bval text);
sqlite> insert into a (akey, aval) values (1, 'a');
sqlite> insert into a (akey, aval) values (2, 'b');
sqlite> insert into b (bkey, fakey, bval) values (1, 1, 'aa');
sqlite> insert into b (bkey, fakey, bval) values (2, 1, 'ab');
sqlite> insert into b (bkey, fakey, bval) values (3, 2, 'bb');
sqlite> insert into b (bkey, fakey, bval) values (4, 2, 'bc');
sqlite> insert into b (bkey, fakey, bval) values (5, 2, 'bd');
sqlite> select * from a join b on (akey=fakey);
1|a|1|1|aa
1|a|2|1|ab
2|b|3|2|bb
2|b|4|2|bc
2|b|5|2|bd
sqlite> select * from a join b on (akey=fakey)
   ...> where bkey=(select bkey from b where akey=fakey order by bval limit 1);
1|a|1|1|aa
2|b|3|2|bb
sqlite> select * from a join b on (akey=fakey)
   ...> where bkey=(select bkey from b where akey=fakey order by bval desc limit
 1);
1|a|2|1|ab
2|b|5|2|bd