CODEWITHSUNDEEP
mysqlsqlsql-server
Oak
Oak

Reputation: 471

Sql Server Max() over partition by - translate into MySql

So I've seen SQL Servers RowNumber() over(partition by id) convert to MySql from another question posted here

But this doesn't help with another aggregate function such as MAX().

I need the Max() of a group of rows. 

Cust_Type   Cust_Name   Revenue  Max
Top         A           10000    10000
Top         B           9000     10000 
Top         C           8000     10000 
Bottom      X           5000     7000
Bottom      Y           6000     7000
Bottom      Z           7000     7000

Now I know I can do a query to get the Max and Cust_Type and then join that to the whole table to add on the max value, but is there a way to not do that extra query but instead do it in the same fashion as the post I referenced?

Upvotes: 2

Views: 2884

Answers (1)

sgeddes
sgeddes

Reputation: 62861

If I'm understanding correctly, you could use user-defined-variables.

select cust_type, 
       cust_name,
       revenue,
       @max:=IF(@custtype!=cust_type,revenue,@max),
       @custtype:=cust_type
from yourtable, (select @max:=0, @custtype:='') t
order by cust_type, revenue desc

Upvotes: 3

Related Questions