CODEWITHSUNDEEP
mysqlsql
Jin Kwon
Jin Kwon

Reputation: 21996

SQL ranking view and group by

Can anybody please tell me how to create a view which has a additional RANK column for a value column grouped by some other columns?

ID
BALANCE
GROUP2
GROUP1
GROUP0
unique (GROUP0, GROUP1, GROUP2)

The problem is the 'RANK' that the VIEW will calculate should be grouped by those GROUPs (say 0, 1, 2).

ID
BALANCE
GROUP2
GROUP1
GROUP0
RANK

Thanks.

EDIT ----------------------- I'm sorry I should explain more. The RANK column is the `ranking by balance' for each group of (GROUP0, GROUP1, GROUP2)

So I want a vew looks like

ID  BALANCE  GROUP0  GROUP1  GROUP2  RANK
-----------------------------------------
    1000     0       0       0       1
    999      0       0       0       2
    1000     1       0       0       1
    999      1       0       0       2

Thanks.

Upvotes: 0

Views: 308

Answers (1)

Florin Ghita
Florin Ghita

Reputation: 17643

Not a clear question. Is this what you want?

Select 
  id,
  balance,
  group1,
  group2,
  group3,
  row_number() over(partition by group1, group2, group3) as rank
from your_table.

You can add an order by in the over clause as needed, for example:

     row_number() over(partition by group1, group2, group3 order by id) as rank

Upvotes: 2

Related Questions