SQL Server Performance

Query

Discussion in 'T-SQL Performance Tuning for Developers' started by bugatha, Apr 29, 2007.

  1. bugatha New Member


    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.
  2. Roji. P. Thomas New Member

    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
  3. bugatha New Member

    I am getting an error:

    Incorrect syntax near the keyword 'ON'
  4. bugatha New Member

    Please reply back..
  5. Madhivanan Moderator

    Are you using SQL Server?
    Make sure you used braces in correct place

    Madhivanan

    Failing to plan is Planning to fail
  6. bugatha New Member

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

    Which version of SQL Server you are using?
    What is the service pack level on SQL Server?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  8. Roji. P. Thomas New Member

    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
  9. bugatha New Member

    Thanks Roji..

Share This Page