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

[Gyepi SAM]

Hello there,

This puzzle can be solved without using a cursor. In fact, I was able
to find two solutions: one that solves the initial problem, and another
for the general case.
The examples were done using SQLite, but that should not matter too much.

Given the following data definitions:

  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);

This query returns the first matching rows from table B:

  select b.data from
  a inner join b on (a.pk = b.fk)
  group by a.pk
  order by b.data;

It works, but the grouping limits you to only getting the first rows and
you're out of luck if you want more than that.


This query returns the first n matching rows, in this case n == 2.
Unlike the first query, this one requires table B to have a primary or candidate key.
If there isn't one, and your SQL product provides a rowid pseudo field, you
could use that instead. As a last resort, you could manufacture a field like concat(b.fk, '-', b.data) and
group on that, but it would be quite a bit slower. Indexing would help, of
course.

  select b.data from
  a inner join b on (a.pk = b.fk)
  group by b.pk
  having (count(*) <= 2) 
  order by b.data;


HTH

-Gyepi

On Tue, Dec 13, 2005 at 06:50:08PM -0500, Ben Liyanage wrote:
>    ** Be a Good Dobee and help the group, you must be counted to post .
>    ** Fill out the survey/skills inventory in the member's area.
> I have a sql challenge for those of you who wish to accept it.
> 
> 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.
> 
> The use of cursors will be considered cheating.  I think this is possible,
> but I am open to someone proving it is not as well.
> 
> Good luck.
> 
> Ben Liyanage
> ben at smartankgroup.com
> 410.336.2464
> 
> _______________________________________________
> Hidden-discuss mailing list - home page: http://www.hidden-tech.net
> Hidden-discuss at lists.hidden-tech.net
> 
> You are receiving this because you are on the Hidden-Tech Discussion list.
> If you would like to change your list preferences, Go to the Members   
> page on the Hidden Tech Web site.
> http://www.hidden-tech.net/members