none
Return a row based on the max value of the column

    Frage

  • 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

Antworten

  • 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

    WITH CTE AS 
    (  
    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