Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    6

    Unanswered: Get the Row which has Max value of the field

    I want to get a field value in the row, which has max value of another field in the same table

    I have done with the below query. I want to know is there any other better way of doing it becuase it is taking longer time.

    Thanks for your help


    SELECT Field1
    FROM TableName
    WHERE Field2 = ( SELECT MAX(field2)
    FROM TableName)

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That shouldn't take too long to process. How many rows, and do you have an index on Field2?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2004
    Posts
    6
    I have about 200k records. I have index (non-clustered )on field2. More over it is taking about 7 sec to run the above query

    Thanks

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and of those 200K records, how many are typically being returned in the result set? It may take several seconds to display the result set.

    Have you tried viewing the query plan?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •