Stack Exchange
Get the row with max(column) for distinct key
I have some data like
code    amount  month
aaa1    100     1
aaa1    200     2
aaa1    300     3
aaa4    450     2
aaa4    400     3
aaa6    0       2

From the above, for each code I want to get the row with max(month)
code    amount  month
aaa1    300     3
aaa4    400     3
aaa6    0       2

How can I create a ms sql query for this?

;WITH MyCTE AS
(
    SELECT code,
           amount,
           month,
           ROW_NUMBER() OVER(PARTITION BY code ORDER BY code,month DESC) AS rownum
    FROM   table
)
SELECT *
FROM   MyCTE 
WHERE  rownum = 1


why is there a semicolon in front of WITH? Is the syntax the same for python sqlite?

@user3358205 From the MSDN https://msdn.microsoft.com/en-us/library/ms175972.aspx in the remarks section it says: When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

@user3358205 The semicolon is not really needed when WITH is the first word in the statement, but preceding it with the semicolon ensures that if there is any other statement before that it is terminated. Just to be clear, the semicolon should not be the first character for defining a CTE, but rather the last character (terminator) of the previous statement.

@user3358205 Having it first is just a failsafe.

You can use the ranking function ROW_NUMBER() with PARTITION BY code ORDER BY month DESC to do this:
WITH CTE
AS
(
   SELECT 
     code, amount, month,
     ROW_NUMBER() OVER(PARTITION BY code 
                       ORDER BY month DESC) AS RN
   FROM Tablename
)
SELECT code, amount, month
FROM CTE
WHERE RN = 1;

This will give you the maximum month for each code.

SQL Fiddle Demo


Try this
SELECT * 
FROM 
  (SELECT MAX(MONTH) month, code
  FROM table1
  GROUP BY code) res
JOIN table1
ON res.month = table1.month 
AND res.code = table1.code

Here is the SQLfiddle
