SQL Server - Return Max Value In One Column for All Rows

Question

I could use some help with SQL to solve the following problem.

I have a table that has the following columns and data.

CUSTOMER PAYMENT_YEAR BILLS_PER_YEAR BILL_NUMBER
Chris    2010         1              1
Chris    2010         2              2
Chris    2010         3              3

I would like to return all three rows but with the max bills_per_year value in all 3 rows. It would like like this.

CUSTOMER  PAYMENT_YEAR  BILLS_PER_YEAR  BILL_NUMBER
Chris     2010          3               1
Chris     2010          3               2
Chris     2010          3               3

inner join sub query did not work as it just returned the one row with a 3 for bills_per_year. — Chris Bittakis
– Chris Bittakis, Commented Jan 9, 2013 at 4:21
max on just bills_per_year does not work as I have to keep the bill_number in the group by. — Chris Bittakis
– Chris Bittakis, Commented Jan 9, 2013 at 4:21

a1ex07 · Accepted Answer · 2013-01-09 04:21:58Z

2

Will it work for you?

SELECT CUSTOMER, PAYMENT_YEAR, BILLS_PER_YEAR, BILL_NUMBER,
MAX(BILLS_PER_YEAR) OVER (PARTITION BY CUSTOMER,PAYMENT_YEAR) as max_per_year
FROM table1

answered Jan 9, 2013 at 4:21

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Bittakis Over a year ago

select SLX_ACCOUNTID, ASSET_NUMBER, PAYMENT_YEAR, BILL_NUMBER, MAX(BILLS_PER_YEAR) OVER(PARTITION BY SLX_ACCOUNTID, ASSET_NUMBER, PAYMENT_YEAR) BILLS_PER_YEAR FROM TABLE;

TechDo · Accepted Answer · 2013-01-09 04:27:57Z

1

Is this you mean?

SELECT 
    CUSTOMER, 
    PAYMENT_YEAR, 
    MAX(BILLS_PER_YEAR) OVER(PARTITION BY CUSTOMER, PAYMENT_YEAR) BILLS_PER_YEAR,
    BILL_NUMBER
FROM TableName

edited Jan 9, 2013 at 4:27

answered Jan 9, 2013 at 4:22

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

Collectives™ on Stack Overflow

SQL Server - Return Max Value In One Column for All Rows

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related