7

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. :(

Ben
  • 54,723
  • 49
  • 178
  • 224

4 Answers4

11

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
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
4

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

SELECT DISTINCT SUBSTRING_INDEX(animal, ' ', 1) FROM table_name;
Srini V
  • 11,045
  • 14
  • 66
  • 89
Kosta Kontos
  • 4,152
  • 7
  • 25
  • 28
  • nice! here is the doc for SUBSTRING_INDEX: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index – Renaud Nov 07 '12 at 09:47
1

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

Nik
  • 7,113
  • 13
  • 51
  • 80
  • Use @Parkyprg if you need the largest number in the table. Use mine if you don't need the number at all. – Nik Jun 10 '11 at 04:43
  • 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. – Ben Jun 10 '11 at 04:45
  • Do you have an ID for each row? – Nik Jun 10 '11 at 04:47
  • Sure do (well, an auto-increment primary key reference). – Ben Jun 10 '11 at 04:47
  • 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...http://stackoverflow.com/questions/2739474/how-to-select-the-first-row-for-each-group-in-mysql – Ben Jun 10 '11 at 05:04
  • 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. – Nik Jun 10 '11 at 05:24
  • 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 Jun 10 '11 at 05:30
1
SELECT Name, Max(No)
FROM Table
Group By Name
CristiC
  • 22,068
  • 12
  • 57
  • 89
  • This is a pretty good solution too, although generic (my original question was more specific). But came back to it and still helpful. +1 – Ben Jul 09 '11 at 08:51