SQL to select top most column with specific condition

I have an invoice table like this:

Bill    Item    Qty Weight  Rate     Amount Advance
10001   Dal     1   10.00  开发者_运维技巧   70.00  700.00 500.00
10001   Jeera   2    0.60   1200.00  720.00 500.00
10002   Bread   1    0.80     50.00   40.00   0.00
10003   Coffee  1    1.00    700.00  700.00   0.00
10004   Tea     1    0.70    900.00  700.00   0.00

Please notice that for Bill No 10001, the total advance is only 500, but it is repeating twice for item- Dal and Jeera. I need an SQL to show only one advance amount against Dal or Jeera which has the maximum amount like this:

Bill    Item    Qty Weight   Rate    Amount Advance
10001   Dal     1   10.00     70.00  700.00   0.00
10001   Jeera   2   0.60    1200.00  720.00 500.00
10002   Bread   1   0.80      50.00   40.00   0.00
10003   Coffee  1   1.00     700.00  700.00   0.00
10004   Tea     1   0.70     900.00  700.00   0.00

The only change here is for bill no 10001, item Dal the advance amount is made 0. Please help me to achieve this in SQL Server.

Thanks in advance.

---

;with cte as
(
  select *,
    row_number() over(partition by Bill order by Advance desc) as rn
  from YourTable
)
select
  C.Bill,
  C.Item,
  C.Qty,
  C.Weight,
  C.Rate,
  C.Amount,
  case C.rn when 1 then C.Advance else 0 end as Advance
from cte as C

---

Gut feel is that your table is not normalised.

I suggest breaking it up into smaller tables

Possible something like :

+- Bills -------+
|               |
|  BillNo INT   |
+---------------+

+- Items ------------+
|                    |
|  Item varchar(20)  |
+--------------------+

+- BillItems --------+
|                    |  
|  BillNo INT        |
|  Advance MONEY     |
+--------------------+

Then you store the amount advanced against the most granular object in your table, namely the "Item".