CODEWITHSUNDEEP
sqlsql-serversummax
michael donte
michael donte

Reputation: 17

How to choose max of one column per other column

I am using SQL Server and I have a table "a"

month  segment_id   price
-----------------------------
  1      1            100       
  1      2            200     
  2      3             50     
  2      4             80      
  3      5             10

I want to make a query which presents the original columns where the price will be the max per month

The result should be:

month  segment_id   price
----------------------------     
  1      2            200        
  2      4             80      
  3      5             10

I tried to write SQL code:

Select 
    month, segment_id, max(price) as MaxPrice
from 
    a

but I got an error:

Column segment_id is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

I tried to fix it in many ways but didn't find how to fix it

Upvotes: 1

Views: 75

Answers (4)

John Cappelletti
John Cappelletti

Reputation: 81970

Only because it was not mentioned.

Yet another option is the WITH TIES clause.

To be clear, the approach by Gordon and Barbaros would be a nudge more performant, but this technique does not require or generate an extra column.

Select Top 1 with ties *
 From  YourTable 
 Order By row_number() over (partition by month order by price desc)

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65313

Because you need a group by clause without segment_id

Select month, max(price) as MaxPrice 
  from a
 Group By month

as you want results per each month, and segment_id is non-aggregated in your original select statement.

If you want to have segment_id with maximum price repeating per each month for each row, you need to use max() function as window analytic function without Group by clause 

Select month, segment_id, 
       max(price) over ( partition by month order by segment_id ) as MaxPrice 
  from a

Edit (due to your lastly edited desired results) : you need one more window analytic function row_number() as @Gordon already mentioned:

Select month, segment_id, price From
(
Select a.*,
       row_number() over ( partition by month order by price desc ) as Rn
  from a
  ) q
Where rn = 1

Upvotes: 3

forpas
forpas

Reputation: 164099

With not exists:

select t.*
from tablename t
where not exists (
  select 1 from tablename
  where month = t.month and price > t.price
)

or:

select t.*
from tablename inner join (
  select month, max(price) as price 
  from tablename 
  group By month
) g on g.month = t.month and g.price = t.price

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I would recommend a correlated subquery:

select t.*
from t
where t.price = (select max(t2.price) from t t2 where t2.month = t.month);

The "canonical" solution is to use row_number():

select t.*
from (select t.*,
             row_number() over (partition by month order by price desc) as seqnum
      from t
     ) t
where seqnum = 1;

With the right indexes, the correlated subquery often performs better.

Upvotes: 1

Related Questions