3

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?

Community
  • 1
  • 1
Pez Cuckow
  • 14,048
  • 16
  • 80
  • 130

2 Answers2

2

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
            )
Robert
  • 25,425
  • 8
  • 67
  • 81
  • 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! – Pez Cuckow Sep 11 '12 at 12:57
  • 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. – Robert Sep 12 '12 at 07:52
-1

Try this and let me know if it works:

select distinct breed, max(id) as id , max(type) as type
from animals

You may have to play around with max() The arbitrary choice here is max(), but you could arbitrarily use min() instead. max() returns the largest value for that columns, min() the smallest

Wouter J
  • 41,455
  • 15
  • 107
  • 112
Edward Chapman
  • 11