Get selected values from one table, sort, copy and past into another table

[#1]

Hi,

I have two tables. The one i'm calling the big table has part numbers, prices, BLS index codes, and BLS index names. This table has a couple million rows duplicating the part number but different prices and BLS indices.

My small table has a few hundred rows of unique part numbers. Columns for BLS index and name, not populated.

I'd liketo populate the BLS index and name in small table with the BLS index and name from the same part number in the big table of the row with the highest price.

My plan was to:

1-select first part number from small table

2-select all rows in big table with matching part number.

3-Make a new table with the four columns

4-sort new table on price

5-select the highest prices BLS index code and name from the sorted table and paste into the smaller table corresponding to the part number.

Here's what i have but i'm stuck at trying to make the new table because i don't know how to copy more columns over. Any help would be greatly appreciated.

dt2= data table("v5.8_bigTable");

dt1= data table("smallTable");

for(i=1, i<=2, i++,

     if(i==1,

          dt2<< Select Where(dt2:partNumber == dt1:partNumber[i]);

          selRows=N row(dt2<<Get Selected Rows);

          dtsub=dt2<<subset(output table name("partNumber"||char(i)));

          dtsub<<Sort(By(:Total Price),Order(descending)):

     )

);

[#2 · Accepted solution]

I think there is an easier way

1. Take your small data table, with just the Part number, and join that table to the large data table.  It will create a new table with only the part numbers found in the small data table.
2. Create a summary data table, grouping on part number, BLS code and name, and specify the statistic Maximum for the price.
3. What comes out of the Summary should be the small data table you want.