SQL select distinct substring where like muddleup howto

Question

I've got a table with a field that is similar to this:

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

The field may later have new animals added, such as

GOAT 72
DOG 20

What I'd like to do is make a SELECT query that returns one unique row per animal name, returning a data set with one row per type of animal, which I can later parse to make a list of animals present in my table.

So, after the magic, I'd have

FISH 54
DOG 12
CAT 65
BIRD 10
GOAT 72

...from which I would make my list.

So far, I've been muddling around with subqueries, SELECT DISTINCT, and SUBSTRING(), but I feel that any result I came up with would probably pale in comparison to the might of the SO hive mind. Can someone help?

UPDATE

If it helps, my failed attempt sort of illustrates what I want to do:

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

only now I don't have the whole name, just a substring. :(

Solution 1

This will work for SQL Server. If you use something else you need to figure out the corresponding functions to left and charindex. Left could of course be replaced with a substring.

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

Result:

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

In MySQL you would use left and locate. (Code not tested)

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

Solution 2

You can also use the following to get your list of animal names:

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

Solution 3

Why not just

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

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

If you don't need so select the value, then just do:

SELECT animal FROM table GROUP BY animal

Solution 4

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

View more solutions

Comments

[Nik]

Use @Parkyprg if you need the largest number in the table. Use mine if you don't need the number at all.

[Ben]

I need yours, but only the first code of each group. That's a great start though I'll go hit the books - maybe max would come in handy.

[Nik]

Do you have an ID for each row?

[Ben]

Sure do (well, an auto-increment primary key reference).

[Ben]

I see where you're going with this but sorry still doesn't work, something is wrong in the HAVING...also I don't think I need to select value field at the start? Will keep tweaking...stackoverflow.com/questions/2739474/…

[Nik]

If you don't need to select the value, just use my original suggestion. That will get you a list of unique animals in the table.

[Ben]

The problem is, all of the animals are unique, so they each make their own group: DOG 10 and DOG 11 will not group into DOG. But I'll find a new way to do it. Thanks for your help, I'll +1 one of your other answers (to a different question). I'll delete this question in an hour or so.

[Ben]

Closer and closer. That's exactly what I'm looking for...but I'm running MySQL.

[Ben]

You have done it, my good man. Thanks muchly. I've been badge whoring today so I used all my upvotes, but you'll get a +1 too, in time.

[Andriy M]

Actually, you are getting one now. :)

[Ben]

Coming back to this a while later, this bit of code is hugely helpful, thanks again.

[Ben]

This is a pretty good solution too, although generic (my original question was more specific). But came back to it and still helpful. +1

[Renaud]

nice! here is the doc for SUBSTRING_INDEX: dev.mysql.com/doc/refman/5.0/en/…

[Libin TK]

How do I select the count also?