Supreme Grand Ruler Supreme Grand Ruler - 6 months ago 7
SQL Question

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.

Answer

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