Query

I have a table ServiceRequest, which has a column 'rowid'. There is another table named 'Activity', it has a column 'srid'.
These two tables have a primary and foreign key relationship on the above said coulmns
'rowid' and 'srid'.

Each 'rowid' in 'ServiceRequest' table have a no of corresponding 'activity' records in the 'activity' table.

this activity table have a column 'created'. I am trying to pull all the records which have the min(created)in activity table for corresponding 'rowid'of the table ServiceRequest.

Please help me.

Sunder.

SELECT S.*, A.*
FROM ServiceRequest S
INNER JOIN
(SELECT srID, MIN(Created)
FROM Activity
GROUP BY srID) A
ON A.srID = S.rowId


Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

I am getting an error:

Incorrect syntax near the keyword 'ON'

Please reply back..

I am executing the below query give by thomas and getting the above said error while executing it

SELECT S.*, A.*
FROM ServiceRequest S
INNER JOIN
(SELECT srID, MIN(Created)
FROM Activity A
GROUP BY srID)
ON A.srID = S.rowId

The query should be

SELECT S.*, A.*
FROM ServiceRequest S
INNER JOIN
(SELECT srID, MIN(Created) AS created
FROM Activity A
GROUP BY srID) A
ON A.srID = S.rowId

Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

Thanks Roji..