How to update Flag based on Maximum Amount

• avdhut.k

  SSCrazy

  Points: 2834

  More actions

  August 13, 2013 at 1:28 am

  #1640812

  Create Table #Temp

  (

  Lnno Varchar(15),

  Custcode varchar(10),

  Flag varchar(10),

  Amount Int,

  Amount_Flag varchar(1)

  )

  Insert Into #Temp

  Values ('1','A1','Cust',1000,''),

  ('1','A1','Cust',1000,'')

  Select * from #Temp

  /*

  Hi,

  As per my requirement it is working all right but only in one scenario it is not working,that I mentioned in sample data,

  For Lnno = 1,

  Custcode = A1

  flag = Cust

  And Amount is also same,then it is Updated Amount_Flag = 'Y' for both the records,

  My requirement is in this case it should be updated to only any one record.

  */

• Stefan_G

  SSCertifiable

  Points: 6609

  More actions

  August 13, 2013 at 2:46 am

  #1640834

  Create Table #Temp

  (

  Lnno Varchar(15),

  Custcode varchar(10),

  Flag varchar(10),

  Amount Int,

  Amount_Flag varchar(1)

  )

  Insert Into #Temp

  Values ('1','A1','Cust',1000,''),

  ('1','A1','Cust',1000,'')

  Select * from #Temp

  ;with cte as (

  Select row_number() over (partition by Lnno order by amount desc, flag desc, custcode) as rn,*

  from #Temp

  )

  update cte

  set Amount_Flag = 'Y'

  where rn=1

  Select * from #Temp

  drop table #Temp

  Returns

  LnnoCustcodeFlagAmountAmount_Flag

  1A1Cust1000Y

  1A1Cust1000

  Isn't this exactly what you want?

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 13, 2013 at 4:09 am

  #1640846

  Stefan's code and my code now return a result set which exactly matches your posted requirements. If your requirements have extended or changed, then please use a sample data set and an expected output set to explain the change. Your English is very difficult to understand.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden