SELECT DISTINCT values and INSERT INTO table
Solution 1
You can use INSERT INTO... SELECT
statement on this,
INSERT INTO tableName (A, B, C)
SELECT A, B, MAX(C) + 1
FROM tableName
GROUP BY A, B
Solution 2
Try this:
SELECT * INTO new_table FROM
(SELECT DISTINCT * FROM table_name) x
JohnAtrik
Updated on July 09, 2022Comments
-
JohnAtrik almost 2 years
I want to take a column with values that repeat multiple times and get that value only once and store it for later use, but at the same time I would like to get another value in the same row as that distinct column.
A B C 32263 123456 44 32263 123456 45 32263 123456 46 32264 246802 44 32263 246802 45 32264 246802 46 32265 369258 44 32265 369258 45 32265 369258 46
A, B, C represent three columns. Ignore C for now.
My question is: How can I get this information in this table and store it for I can use it later in the script?
Here is what I tried:
use databaseName select distinct A from tableName order by A
The result is:
A 32263 32264 32265
I'm trying to get it to also give me B's value. (Note it does not matter at all which row I get since no matter what A I choose the value of B will be the same for given A.) We are ignoring C for now.
The result should be:
A B 32263 123456 32264 246802 32265 369258
Now, once I get it like that I want to insert a row using the values I got from the query. This is where C comes in. I want to do something like this:
use databaseName insert into tableName (A, B, C) values (32263, 123456, 47)
Of course I don't want to put the values directly inside of there, instead have some type of loop that will cycle through each of the 3 distinct A values I found.
In short, my table should go from:
A B C 32263 123456 44 32263 123456 45 32263 123456 46 32264 246802 44 32263 246802 45 32264 246802 46 32265 369258 44 32265 369258 45 32265 369258 46
To:
A B C 32263 123456 44 32263 123456 45 32263 123456 46 32263 123456 47 - 32264 246802 44 32263 246802 45 32264 246802 46 32264 246802 47 - 32265 369258 44 32265 369258 45 32265 369258 46 32265 369258 47 -
I placed dashes next to the newly added rows to help you see the changes.
I figure I should perhaps do some type of loop that will cycle through all three distinct A values, but my problem is how to do that?
Thanks for your time.
-
Hart CO over 10 yearsThis would add many duplicates, OP just wants 1 row per grouping.
-
Vulcronos over 10 years@GoatCO Updated my query to filter. A group by will work most of the time until you just want to group by A. Just wanted to introduce windowing function since they are very useful.
-
cdsaenz over 4 yearsCool. Opened my eyes to import from a populated table over to a codes table:
INSERT INTO code_mst (code_value,code_field) SELECT DISTINCT app_brand,'app_brand' FROM app_mst