Hello,

I have a problem that should be very simple but I am having a really bad day or something and just can't make it out. Any help will be highly appreciated!

Imagine a table with columns ID, ANIMAL, FOOD.

The content of the table is the following:

1, Ape, Banana
2, Pig, JustAboutAnything
3, Bird, Seed
4, Ape, Ecological Banana

Now I want to retrieve all columns but I want the ANIMAL-column to be DISTINCT in nature. For example I only want the first row if there are more occurences of a column-data (like in the example above that features two "Ape"-occurences). I only want the first 3 rows in the example above.

Cheers!

USE TEMPDB; -- Good habit for SQL 2005 is to use ; after every statement
-- Setup Animal Feed test data (some animals feed more equally than others)
CREATE TABLE dbo.AnimalFeed
(ID int
,Animal varchar(20)
,Food varchar(29)
);

INSERT dbo.AnimalFeed VALUES ( 1, 'Ape' , 'Banana' );
INSERT dbo.AnimalFeed VALUES ( 2, 'Pig' , 'Anything non-metallic' );
INSERT dbo.AnimalFeed VALUES ( 3, 'Bird' , 'Bird Seed' );
INSERT dbo.AnimalFeed VALUES ( 4, 'Ape' , 'Rotten Banana' );
INSERT dbo.AnimalFeed VALUES ( 5, 'Ape' , 'Potato' );
INSERT dbo.AnimalFeed VALUES ( 6, 'Pig' , 'Pig Feed' );
INSERT dbo.AnimalFeed VALUES ( 7, 'Bird' , 'Worms' );

-- Return First row for each animal
SELECT *
FROM dbo.AnimalFeed AF
WHERE AF.ID = (SELECT TOP 1 ID
                FROM dbo.AnimalFeed AF2
                WHERE AF2.Animal = AF.Animal);

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

Thanks a lot David!

I had been experimenting with a much more complex solution with plenty more subqueries but - as always - the solution to what seems to be puzzling problems is normally simple.

Cheers!

Thanks a lot Dave!

Simple and elegant just as it ideally should be!

Cheers,
Thomas

Thomas,

I am glad I could help. Correlated SubQueries are quite elegant, however they can have a performance impact since the subquery effectively runs once for every row of the outer query. In the subquery you should use an order by to determine which row you want returned (the lowest ID, the highest ID, or some other criteria)

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com