Existing SQL Server 2008 script improvement -

sql server 2008 have 2 tables orderids , itemids. need resulting table each orderid forst table linked orderid second table number of identical itemids maximum.

i did script using 2 loops if number of orderids in tables big (~1000) means loop has run 1000x1000 times, might long. ca achieved in better way?

see below written script:

drop table #match, #orderrec, #ordersent  create table #match(     orderidrec  int null,     orderidsent int null)  create table #orderrec(     orderidrec      int     not null,     itemid          int         null)  create table #ordersent(     orderidsent     int     not null,     itemid          int         null)  insert #orderrec values (1, 1) insert #orderrec values (1, 5) insert #orderrec values (1, 7) insert #orderrec values (1, 4) insert #orderrec values (1, 15) insert #orderrec values (1, 10)  insert #orderrec values (2, 21) insert #orderrec values (2, 15) insert #orderrec values (2, 21) insert #orderrec values (2, 26)  insert #orderrec values (5, 4) insert #orderrec values (5, 3) insert #orderrec values (5, 12) insert #orderrec values (5, 1)  insert #ordersent values (121, 1) insert #ordersent values (121, 2) insert #ordersent values (121, 5) insert #ordersent values (121, 10) insert #ordersent values (121, 9)  insert #ordersent values (122, 6) insert #ordersent values (122, 7) insert #ordersent values (122, 9) insert #ordersent values (122, 11)  insert #ordersent values (142, 1) insert #ordersent values (142, 12) insert #ordersent values (142, 4) insert #ordersent values (142, 11)  set nocount on  declare @orderidrec int,         @orderidsent int,         @cnt numeric(10),         @cnt_max numeric(10),         @orderidsentmax int  select @orderidrec = min(orderidrec) #orderrec  while isnull(@orderidrec,0) > 0 begin      select @orderidsent = min(orderidsent)     #ordersent      set @cnt_max = 0     set @orderidsentmax = null      while isnull(@orderidsent,0) > 0     begin          set @cnt = 0          select @cnt = count(*)         #orderrec r             inner join #ordersent t         on t.itemid = r.itemid         r.orderidrec = @orderidrec         , t.orderidsent = @orderidsent          if isnull(@cnt, 0) > @cnt_max         begin             set @cnt_max = @cnt             set @orderidsentmax = @orderidsent               end          select @orderidsent = min(orderidsent)         #ordersent         orderidsent > @orderidsent     end      insert #match(         orderidrec,         orderidsent)     values (@orderidrec, @orderidsentmax)      select @orderidrec = min(orderidrec)     #orderrec     orderidrec > @orderidrec end  select *  #match order orderidrec 

the actual script starts set nocount on, before create set of data play with.

the result is:

orderidrec  orderidsent 1            121 2            null 5            142 

;with s  (   select orderidrec, orderidsent,      rn = row_number() on (partition orderidrec order c desc)       (     select r.orderidrec, s.orderidsent,        c = count(*) on (partition r.orderidrec, s.orderidsent)     #orderrec r     inner join #ordersent s     on r.itemid = s.itemid   ) s2 ), d (select orderidrec #orderrec group orderidrec) select d.orderidrec, s.orderidsent d left outer join s on d.orderidrec = s.orderidrec , s.rn = 1 order d.orderidrec;