How to select a max row for each group in SQL
try this query instead,
WITH OrderedOrders AS ( SELECT country,grpid,value,ROW_NUMBER() OVER(PARTITION BY country ORDER BY country,value DESC) AS 'RowNumber' FROM test1 ) select * from OrderedOrders where RowNumber =1
I believe this is what you're looking for:
;with cte as ( select country, max(value) as MaxVal, min(row_number) as MinRow from test1 group by Country)select c.country, t.grpid, c.MaxVal, c.MinRowfrom cte cjoin test1 t on t.country = c.country and t.value = c.MaxVal and t.row_number = c.MinRoworder by country, grpid
Can you please try out this query
select country, value, grpid, count(*) from test1 group by country, value, grpid order by country, value desc