I want select countries with maximum value of 'Value' for a 'grpid'. Also already selected 'Country' should not be considered for other 'grpid' while checking the maximum. ( ie Country or grpid should not be repeated in the result )
Result:
Country grpid Value Row_number
US 49707 604456458 1
GB 5086 497654945 4
CA 909 353500201 10
JP 231 198291290 15
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.MinRow
from cte c
join test1 t
on t.country = c.country
and t.value = c.MaxVal
and t.row_number = c.MinRow
order by country, grpid
with keyword is useful and link renewed. +1Can you please try out this query
select
country,
value,
grpid,
count(*)
from test1
group by
country,
value,
grpid
order by
country,
value desc
grpid '50147'