On Wed, Dec 14, 2005 at 11:27:32AM -0500, Ben Liyanage wrote: >> From: Bill Carr [mailto:bill at bottlenose-wine.com] >> Sent: Wednesday, December 14, 2005 10:50 AM > >> SELECT >> A.key AS outer_key, A.col1, (SELECT B.col2 FROM B WHERE >> outer_key=B.key ORDER BY some_col LIMIT 1) FROM A > This is what I originally tried to do. I belive when you write a query like > this however, that the alias (outer_key here) is not defined inside the > scope of the subquery. The alias is defined in the inner query. However, MySQL 4.0 and earlier versions, does not support subqueries so this won't work at all. > I think the way this needs to be done, is very similar to this query. The > change that you would need to make would be to change the subquery to a > function call that returns the correct id. This way you can pass in > outer_key as a parameter to the subquery. That should work. In fact, you could solve the problem iteratively within your application as well. > Unfortunately the server I am developing on is mySQL 4.x, and does not > support stored procedures or functions. MySQL has supported functions for quite some time. I have been adding functions to MySQL for at least five years now. Stored procedures were not supported until version 5.0, I think. > Maybe there is another way, I'm still brain storming. There is another way. Since your version of MySQL does not support subqueries, you can use a temporary table to simulate it. Using the following schema: create table A ( pk integer ); insert into A values(1); insert into A values(2); insert into A values(3); create index a_pk on A(pk); create table B ( pk integer, fk integer, data char(15) ); insert into B values (1,1,"apple"); insert into B values (2,1,"banana"); insert into B values (3,1,"cherry"); insert into B values (4,2,"aston"); insert into B values (5,2,"bugati"); insert into B values (6,2,"chrysler"); insert into B values (7,3,"aspen"); insert into B values (8,3,"beech"); insert into B values (9,3,"cedar"); create index b_pk on B(pk); create index b_fk on B(fk); create index b_data on B(data); You can generate the results with the following: This temporary table will only contain the top n records per foreign key. create temporary table b_rank select b2.*, sum(1) as rank from B b1, B b2 where b1.fk = b2.fk and b1.data <= b2.data group by b2.pk having rank <= 2; Indexing is not strictly necessary, but would be useful if you're working with non-trivial amounts of data. create index b_rank_fk on b_rank(fk); create index b_rank_rank on b_rank(rank); create index b_rank_data on b_rank(data); And here's the data you really want. select br.data, br.rank from A inner join b_rank br on (A.pk = br.fk) order by A.pk, br.data; This solution works, but is rather wasteful since you have to create a temporary table each time. There are a few possible optimizations that might help. 1. Add the rank column to the B table and update it upon record changes. This would be an ideal job for a trigger, but your version of MySQL does not support triggers. However, you could emulate on by adding a function that your application calls whenever records are added, deleted, or modified. 2. If you're willing to tolerate some amount of staleness, you could create a real B_rank table that gets updated by a cron task. 3. Combine the two solutions and call the rank_update function from cron so your application does not have to change. HTH -Gyepi