CODEWITHSUNDEEP
mysqlsql
Divyae Vats
Divyae Vats

Reputation: 1

Same column values in a partition as value in Rank 1 row

I want my SQL query to return the same column values in a partition as the value in the same column in the Rank 1 row of the partition.

Example : Below is an example table :

Rank  Name  Class  PocketMoney
----  ----  -----  -----------
1       A     6         6
2       B     6         5
3       C     6         4
1       P     7         7
2       Q     7         6
1       R     8         8
2       S     8         7
3       T     8         6
4       U     8         5

I want the output of my SQL query to be like :

Rank  Name  Class  PocketMoney
----  ----  -----  -----------
1       A     6         6
2       B     6         6
3       C     6         6
1       P     7         7
2       Q     7         7
1       R     8         8
2       S     8         8
3       T     8         8
4       U     8         8

Is this possible in a SQL Query?

Upvotes: 0

Views: 1223

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

If you specifically want the same value as rank = 1, then use a conditional window function:

select t.*,
       max(case when rank = 1 then pocketmoney end) over (partition by class) as pocketmoney_1
from t;

Note: This is a general solution that specifically answers your question. It does what you want even if there is no rank = 1 or if there are ranks less than 1.

Upvotes: 0

Schwern
Schwern

Reputation: 165200

Yes, use first_value to get the value from the first row of the partition.

select
  rank() over w,
  name,
  class,
  first_value(PocketMoney) over w as pocketmoney
from whatever
window w as (partition by class order by name asc)

Upvotes: 1

Related Questions