CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlgreatest-n-per-group
sajid
sajid

Reputation: 293

How to select a max row for each group in SQL

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 )

SQL Fiddle

Result:

Country    grpid        Value           Row_number

US        49707        604456458         1
GB        5086         497654945         4 
CA        909          353500201         10
JP        231          198291290         15

Upvotes: 10

Views: 9377

Answers (3)

Metaphor
Metaphor

Reputation: 6415

I believe this is what you're looking for:

SQL Fiddle

;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

Upvotes: 6

sakthi
sakthi

Reputation: 87

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

Upvotes: 7

sakthi
sakthi

Reputation: 87

Can you please try out this query

select 
    country,
    value,
    grpid,
    count(*) 
from test1 
group by  
    country,
    value,
    grpid 
order by 
    country,
    value desc

Upvotes: 0

Related Questions