TSQL: Get a row which has a max value for a column

Question

I have a table with 2 columns (id, name) with following values:

id    name
---   ---
 1    John
 2    John 
 3    Mary
 4    Mary

For values that are repeated in 'name', I only want to select those rows which have maximum value in 'id'. So my desired output is:

id    name
---   ---
 2    John
 4    Mary

I tried following instructions from this link: Fetch the row which has the Max value for a column but couldn't get it to work.

This is the query I'm using:

select 
    name, id 
from
    (select 
         name, max(id) over (partition by name) max_ID 
     from sometable) 
where 
    id = max_ID

But I'm getting this error:

Incorrect syntax near the keyword 'where'.

Any ideas what I'm doing wrong?

Answer 1

If you alias your subquery you will avoid the syntax error... try putting "AS MyTable" after your closing parenthesis

select name, id 
FROM ( select name, max(id) over (partition by name) max_ID from sometable ) AS MyTable 
where id = max_ID

This might be simpler though:

SELECT name, MAX(id) FROM tablename GROUP BY name

Answer 2

Your subquery has no mandatory alias

.. 
FROM ( select name, max(id) over (partition by name) max_ID from sometable )  t -- alias missing 
..

Answer 3

You are using an alias for an aggregate function in where this is wrong.

Using having you can select the name with more then one row

 select * from my_table where id in (  
 select max(id) from my_table where name in 
 (
   select   name
   from  my_table 
   having count(*) >1 
   group by name )) ;