SQL selects a different substring where, like muddleup howto

Question

I have a table with a field similar to this:

ANIMAL
========
FISH 54
FISH 30
DOG 12
CAT 65
CAT 09
BIRD 10
FISH 31
DOG 10

New animals can be added to the field, such as

GOAT 72
DOG 20

What I would like to do is make a query SELECTthat returns one unique row for the name of the animal, returning a single row data set for the type of animal that I can later analyze to make a list of the animals present in my table.

So after magic I would have

FISH 54
DOG 12
CAT 65
BIRD 10
GOAT 72

... from which I would make my list.

So far I have been confused with subqueries SELECT DISTINCTand SUBSTRING(), but I feel that any result that I came up with is likely to be pale in comparison to the power of a reasonable SO skill. Can anyone help?

UPDATE

, , :

SELECT DISTINCT substring(animal,1,4) FROM table;

, .: (

Answer 1

SQL Server. - , left CHARINDEX. , , .

select distinct left(T.Animal, charindex(' ', T.Animal, 1)-1)
from YourTable as T

:

-------------------------
BIRD
CAT
DOG
FISH

MySQL left locate. ( )

select distinct left(T.Animal, locate(' ', T.Animal, 1)-1)
from YourTable as T

Answer 2

, :

SELECT DISTINCT SUBSTRING_INDEX(animal, ' ', 1) FROM table_name;

Answer 3

SELECT id, animal, value FROM table GROUP BY animal, id HAVING id = MIN(id)

This should give you a list of animals in the table, each animal with the first value entered.

If you do not need to select a value like this, just do:

CHOOSE ANIMALS FROM GROUP GROUP ANIMAL

Answer 4

SELECT Name, Max(No)
FROM Table
Group By Name