CODEWITHSUNDEEP
sqlsql-servert-sql
user3879250
user3879250

Reputation:

How can I insert values as a distinct column?

I have a table of about 50k records. It looks something like this:

Animal | Name | Color | Legs
Cat    |George| Black | 4
Cat    | Bob  | Brown | 4
Cat    | Dil  | Brown | 4
Bird   | Irv  | Green | 2
Bird   | Van  | Red   | 2

etc.

I want to only insert Cat once and Bird only once and so on. The Name / Color / Legs etc. should be the first value it finds.

This table has 10 columns and 50k rows.

I tried insert into MyNewTable Select Distinct * From MyAnimalTable, but that didn't work. I also tried group by, but did not work either.

Upvotes: 3

Views: 89

Answers (5)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Use ROW_NUMBER to number the rows per animal and only keep the ones numbered 1.

insert into mynewtable (animal, name, color, legs)
select animal, name, color, legs
from
(
  select 
    animal, name, color, legs,
    row_number() over (partition by animal order by animal) as rn
  from myanimaltable a
) numbered
where rn = 1;

(This numbers the records per animal arbitrarily. So you get the first record per animal "the DBMS finds". If you want any certain order, you'd have to specify this after the partition clause.)

Upvotes: 2

Pankaj Kumar
Pankaj Kumar

Reputation: 47

Best simple solution for your query

select 
Animal,
(select top 1 name from MyAnimalTable where animal=T.animal)  Name,
(select top 1 Color from MyAnimalTable where animal=T.animal) Color,
(select top 1 Legs from MyAnimalTable where animal=T.animal)  Legs
from MyAnimalTable T group by Animal

Upvotes: 0

Vivek Khandelwal
Vivek Khandelwal

Reputation: 314

You can try the following:

INSERT INTO MYNEWTABLE 
select  Animal,Name,Color,legs from
(select distinct Animal,Name,Color,legs,dense_rank() over(partition by animal)
as rnk from table) AS T

Upvotes: 0

StackUser
StackUser

Reputation: 5398

Try like this,

SELECT A.Animal
    ,B.NAME
    ,C.color
    ,A.Legs
FROM (
    SELECT DISTINCT Animal
        ,Legs
    FROM tablename
    ) A
CROSS JOIN (
    SELECT DISTINCT NAME
    FROM tablename
    ) B
CROSS JOIN (
    SELECT DISTINCT Color
    FROM tablename
    ) C

Upvotes: 0

error_handler
error_handler

Reputation: 1201

you can use group by only on animal name and select the rest of the column from Max() to get the first finding.

insert into MyNewTable 
Select MAT.Animal,max(MAT.Name),max(MAT.Color),max(MAT.Legs)
From MyAnimalTable MAT GROUP BY MAT.Animal

Upvotes: 2

Related Questions