Return a row based on the max value of the column


  • Hi,

    I am new to SQL programming.  What I want to do is return an entire row from a table, based on the fact that that row has the maximum value for a particular column.  Something like:

    SELECT * FROM table where field = max(field)

    Obviously, this statement doesn't work, because this is SQL, and nothing is simple or intuitive with SQL it seems. 

    Can anyone set me on the right track?

    Mittwoch, 28. Januar 2009 13:16


  • Edit :  I guess I missunderstood something, if you want single row based on max value

    SELECT TOP 1 ID, Field

    FROM TableName

    order by Field DESC


    Original post :

    Hi you can do followings -

    If you are using sql Server 2005/2008

    select *, ROW_NUMBER() OVER (Partition by Id ORDER  BY Field DESCAS seq     
     from TableName  
    SELECT *   
    FROM CTE   
    WHERE SEQ=1 

    For Sql Server 2000

    select *     
     from TableName AS O     
     WHERE Field = (SELECT MAX(I.Field) FROM TableName AS I     
                        WHERE O.Id = I.Id)  


    Mangal Pardeshi
    SQL With Mangal
    Mittwoch, 28. Januar 2009 13:22