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

[Mark Bucciarelli]

On Tue, Dec 13, 2005 at 06:50:08PM -0500, Ben Liyanage wrote:

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

Play around with subselects, either in the from clause and/or the where 
clause.

    select 
      a.whatever, 
      c.test
    from 
      a,
      (select 
         test 
       from 
         b 
       where 
         b.fkey = a.id 
         and b.sortfld = (select max(sortfld) from b where b.fkey = a.id)
      ) as c;
        ^^^^^
        not sure about this syntax


may need to play with table aliases to get this to work.  the above sql 
assumes unique sortfld values for a given b.fkey.

You can also investigate use TOP 1 (for SQL-Server), LIMIT (for MySQL) 
and ROWNUM (for Oracle), but that's painful if you care about supporting 
multiple back ends [1].  

m

[1] http://dotnet.org.za/thea/archive/2005/02/22/14715.aspx