how get max batch qty by product with an GOOD EXAMPLE

• dquirion78

  Ten Centuries

  Points: 1240

  More actions

  May 22, 2014 at 1:47 pm

  #285854

  Hi Currently I use this solution, but do you know a best way to do that. thanks

  CREATE TABLE #mytable

  (

  ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

  qty int,

  Batch varchar(10),

  Color varchar(10)

  )

  SET IDENTITY_INSERT #mytable ON

  INSERT INTO #mytable

  (ID, qty, batch, Color)

  SELECT '1001','300','abc','ny235' union all

  SELECT '1002','400','abc','ny235' union all

  SELECT '1003','500','def','ny235' union all

  SELECT '1004','200','eee','ny444' union all

  SELECT '1005','100','eee','ny444' union all

  SELECT '1006','800','fff','ny444'

  SET IDENTITY_INSERT #mytable ON;

  with cte1

  as

  (

  select #mytable.Color,#mytable.Batch,sum(#mytable.qty) as qty

  from #mytable

  group by #mytable.Color,#mytable.Batch

  )

  select max(cte1.qty),cte1.color

  from cte1

  group by cte1.Color

  drop table #mytable

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  May 22, 2014 at 2:12 pm

  #1715860

  That's pretty much what you'd need to do. If you need to sum colors and then pick the max quantity, this works well.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events