2 Replies Latest reply: Dec 16, 2011 8:43 AM by Gonzalo Bianchi RSS

    slow for...next, how to get top n records?

    Gonzalo Bianchi

      Hi everyone...

       

      I have this situation inside a script.

      I will explain the scenario with a simple (yet not logical) example, since the original it's too complicate..

       

      I have a table with 4 columns, A (products), B (sites), C (total sales), D (ranking, build by hand after ordered by total sales).

       

      The problem I'm facing is that I need to get products from sites, ordered by the ranking, but for each different sites.

      In the original data, this table has 2m rows, and B has at least 300 unique values.

       

      So, what I need, is get the first 400 rows where B is equal to 1, the first 400 rows where B is equal to 2, and so on until B equals 100 (this is not exactly this way, B has leaps, I have a table that show the code already loaded).

      Then I have to get the first 300 rows where B is equal to 101, and so on...

       

      This goes, for every each value of B..

       

      My code rigth now, includes a nice for... each reading the table of values from B and looking for the quantity of records needed in the original table.

      This is too slow, last run takes about 40 min.

       

      Ok, maybe it's not slow as hell, but I'm sure there is a better way to accomplish it.

       

      Thank you for reading.

       

      Gonzalo