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

[Gyepi SAM]

Hello again,

It turns out that the general solution I proffered does not work, so I'll
offer another ;)

  select b1.data,
         (select sum(1) from b b2 where b1.fk = b2.fk and b1.data >= b2.data group by b2.fk) as rank
  from a inner join b b1 on (a.pk = b1.fk)
  group by b1.pk having rank <= 2;

This query returns the top two matching records per foreign key in B, sorted
by an associated field.

The idea here is to assign a rank to each matching record in table B
and extract the top n items by rank. The subquery, which implicitly uses a cursor,
can be avoided by adding a rank column to table B. The value could be
maintained with a trigger (assuming your SQL product supports them).

-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