How to Create a New Table with Distinct Values but selecting the Max from another column

user_11168443 0
Viewed 134

I am using Python and SQLite, and I am trying to copy the table to another table. I have a column called InventoryNbr and I also have a column called IndexID. The other columns do not matter at this point but I would like to copy them as well. There are several duplicates of InventoryNbr. I only want distinct Inventory Numbers, but I want the MAX IndexID. I keep using this, and getting the LOWEST IndexID. How can I change this to get the max IndexID?

masterCursor.execute('INSERT INTO MasterV2 SELECT InventoryNbr, IndexListID, col1, col2, col3, col4, col5, col6, Extra FROM Master GROUP BY InventoryNbr ORDER BY IndexListID ASC')

edit:My table looks like:

InventoryNbrs | IndexListID
12345         | 123
12345         | 124
12345         | 125
12346         | 126
12346         | 127
12347         | 128

I want my new table to look like:

InventoryNbrs | IndexListID
12345         | 125
12346         | 127
12347         | 128
sqlite sql python
3 Answers

If I understand correctly, you can use window functions:

INSERT INTO MasterV2   -- you should list the columns here
    SELECT InventoryNbr, IndexListID, col1, col2, col3, col4, col5, col6, Extra
    FROM (SELECT m.*,
                 ROW_NUMBER() OVER (PARTITION BY InventoryNbr ORDER BY IndexListID DESC) as seqnum
          FROM Master m
         ) m
    WHERE seqnum = 1;

The SELECT returns one row per InventoryNbr. The one row is the row with the largest value of IndexListID.

user_11440350

Use this:

INSERT INTO MasterV2 
SELECT InventoryNbr, MAX(IndexListID), col1, col2, col3, col4, col5, col6, Extra 
FROM Master 
GROUP BY InventoryNbr

The above select statement returns for each InventoryNbr the row with the max IndexListID, taking advantage of a documented feature of SQLite.
You can find more here: Simple Select Processing

user_104988280

You can use not exists as follows:

INSERT INTO MasterV2
SELECT t.*
  FROM your_table t
 Where not exists
       (Select 1 from your_table tt
         Where tt.InventoryNbr = t.InventoryNbr and tt.IndexListID > t.IndexListID );
user_115656290
Related