Hi.  I've got a select statement that uses an aggregate clause nested inside
of a subquery.   For my own edification (as well as taking advantage of any
available performance boosts that come with it), I'd like to know how to
rewrite it so it doesn't have a subquery.   Here's the statement:
---------------
SELECT * FROM firsttable ft
    LEFT OUTER JOIN [secondtable] st ON
        Ft.[pkid] = st.[fkid] AND
        st.[pkid] in (SELECT MAX(pkid) FROM secondtable WHERE fkid = st.fkid)
---------------
   
The database schema that I'm working with comes from running these statements:
-------
CREATE TABLE firstTable (
pkid INT PRIMARY KEY IDENTITY,
description VARCHAR(10)
)
GO
CREATE TABLE secondtable (
    pkid INT PRIMARY KEY IDENTITY,
    fkid INT REFERENCES firsttable
)
GO
INSERT firsttable (description)
VALUES ('test1')
GO
INSERT firsttable (description)
VALUES ('test2')
GO
INSERT secondtable (fkid) VALUES (1) -- inserts (1,1) into table
GO
INSERT secondtable (fkid) VALUES (1) -- inserts (2,1) into table
GO

In the end, the result set should look like this:

pkid        description pkid        fkid
----------- ----------- ----------- -----------
1           test1       3           1
2           test2       NULL        NULL

Basically, its a left outer join where I want only One record to be included
in the result set even if there are multiple records in the [secondTable]
table.   I filter out the second qualifying record in the secondTable query
by getting on the max(pkid) from a subquery...   but I have no idea how to do
that inside a join clause.

Any help would be appreciated.

Thanks,

JRF