SQL Server Performance

Get row where X field value is max

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by maxl49, Nov 19, 2008.

  1. maxl49 New Member

    Hi,
    I'm trying to figure out the best way performance wise to do the following
    Let's say I have TABLE1 with fields TEXT_FIELD and NUMBER_FIELD
    TABLE1
    TEXT_FIELD NUMBER_FIELD
    Test1 1
    Test2 2
    Test3 3
    I want to return the WHOLE row which has the max value for Number_Field (I.E. row 3 in the example above)
    Is there a better/quicker way than the following :
    SELECT NUMBER_FIELD, TEXT_FIELD
    FROM TABLE1
    WHERE NUMBER_FIELD=(SELECT MAX(NUMBER_FIELD) FROM TABLE1)
    Thanks
  2. preethi Member

    you can use this:
    SELECT TOp 1 * FROM table1 ORDER BY NUMBER_FIELD DESC
  3. Madhivanan Moderator

    If there is more than a row for max valueSELECT
    TOP 1 WITH TIES * FROM table1 ORDER BY NUMBER_FIELD DESC

Share This Page