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

[Jeremy Dunn]

  This is a fairly common problem.  Typical solution:

  select ...

  from A, B

  where b.fkfield = a.field

  and b.sortfield = (select max(b.sortfield) from B

              where b.fkfield = a.field)

  order by ...    ;

  If there are multiple rows in B for the same sortfield value, for
the same row in A, then you can do something to limit the row to the
first one returned (syntax varies by database), e.g. "order by
b.pkfield limit 1;" .

- Jeremy

  Quoting Ben Liyanage <ben at smartankgroup.com>:

>    ** 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
>


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.hidden-tech.net/pipermail/hidden-discuss/attachments/20051214/e8f11ede/attachment-0005.html