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

Gyepi SAM gyepi-hidden-tec at praxis-sw.com
Sat Dec 17 11:08:51 EST 2005


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



Google

More information about the Hidden-discuss mailing list