ms-sql return max variable by group identifier

Question

I have a table as follows :

ID  HC  Week
1   2   1
1   0   2
2   14  1
2   12  2
3   9   1
3   0   2

the data is generated by (what I believe to be) a complex query.

now what I'm trying to do is wrap this in a sub query and only return the max HC column by ID with its corresponding week.

the best I can do is wrap a max function and group it by ID and pass in theMAX(HC) function as such

SELECT 
  max(HC), 
  ID 
from (sub query) 
group by 
  ID

this gets me very close, but I also want the corresponding week when I pass this in it returns the above table with every key.

   SELECT 
      max(HC), 
      ID,
      Week
    from (sub query) 
    group by 
      ID,
      Week

what I'm after is the following :

    ID  HC  Week
    1   2   1
    2   14  1
    3   9   1

is anyone able to guide me ?

Many thanks @Larnu the first question gets me very close, but I get a `An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.` but I'll do some googling to figure it out! — Umar.H, Aug 06 '19 at 11:42
Sounds like the error is telling you the problem here :) I personally prefer the `ROW_NUMBER` method. — Thom A, Aug 06 '19 at 11:45
@Larnu I find SQL syntax very difficult, I was close to writing this in pandas and creating a new table but I think it's overkill (and bad practice!) — Umar.H, Aug 06 '19 at 11:47
If you are using MSSQL 2017 or newer, you can use the aggregate function FIRST_VALUE instead of MAX on both HC and Week. You just need to add and ORDER BY ID DESCENDING. — Marc Guillot, Aug 06 '19 at 12:08
`FIRST_VALUE` arrived in SQL Server 2012, not 2017, @MarcGuillot :) — Thom A, Aug 06 '19 at 17:26
I did try first value, wasn't recognised !I have reporting studio 2018 not sure about the SQL server itself, really appreciate your help — Umar.H, Aug 06 '19 at 17:28

0 Answers0