Sybase SQL Select Distinct Based on Multiple Columns with an ID

Question

I'm trying to query a sybase server to get examples of different types of data we hold for testing purposes.

I have a table that looks like the below (abstracted)

Animals table:
id | type | breed           | name
------------------------------------
1  | dog  | german shepard  | Bernie
2  | dog  | german shepard  | James
3  | dog  | husky           | Laura
4  | cat  | british blue    | Mr Fluffles
5  | cat  | other           | Laserchild
6  | cat  | british blue    | Sleepy head
7  | fish | goldfish        | Goldie

As I mentioned I want an example of each type so for the above table would like a results set like (in reality I just want the ID's):

id | type | breed           
---------------------------
1  | dog  | german shepard  
3  | dog  | husky          
4  | cat  | british blue   
5  | cat  | other          
7  | fish | goldfish    

I've tried multiple combinations of queries such as the below but they are either invalid SQL (for sybase) or return invalid results

  SELECT id, DISTINCT ON type, breed FROM animals
  SELECT id, DISTINCT(type, breed) FROM animals
  SELECT id FROM animals GROUP BY type, breed

I've found other questions such as SELECT DISTINCT on one column but this only deal with one column

Do you have any idea how to implement this query?

Solution

Maybe you have to use aggregate function max or min for column ID. It will return only one ID for grouped columns.

select max(Id), type, breed 
from animals
group by type, breed 

EDIT:

Other different ways to do it:

With having and aggregate function

select id, type, breed  
from animals 
group by type, breed  
having id = max(Id)

With having and aggregate subquery

select id, type, breed 
from animals a1
group by type, breed 
having id = (
               select max(id)
               from animals a2
               where a2.type = a1.type
               and   a2.breed = a1.breed
            )

Comments

[Pez Cuckow]

This worked as expected but took around 20 mins to run, will leave the question open searching for a more elegant (and speedy) solution but will accept if noone has any other suggestions!

[Robert]

I found other ways to do it. I'm not sure if it will work faster, maybe second proposition. Maybe first suggestion is more readable. I edited the post.