Query question

[afronaut]

Hey all, I've got data structured like this: ID - Contact - Company - Email 1 - Jeff - Acme - Jeff@Acme.com 2 - Hester - Acme - Hester@Acme.com 3 - Mort - Acme - Mort@Acme.com 4 - Jason - Widget - Jason@Widget.com 5 - Harriet - Widget - Harriet@Widget.com I need to write a query that will return only the first contact for each company, in this case it should return: ---------------------------------------- 1 - Jeff - Acme - Jeff@Acme.com 4 - Jason - Widget - Jason@Widget.com Thanks in advance! *->>Always working on my game, teach me *->>something new. cout << "dav1d\n";

[Dr_X]

Not Tested but give it a try. SELECT ID, Contact, Company, Email FROM [table] GROUP BY Company HAVING MAX(ID) = ID Michael I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. Vince Lombardi (1913-1970)

[miah alom]

Just a little correction SELECT ID, Contact, Company, Email FROM [table] GROUP BY Company HAVING MIN(ID) = ID

[Johny Ng]

Nope:suss: something like select * from [table] t where id=(select min(id) from [table] tt where t.company=tt.company) HTH

[miah alom]

Check this SELECT * FROM DeleteMe GROUP BY Company HAVING MIN(ID) = ID

[Johny Ng]

It will nerver work. Read BOL about GROUP BY first before posting.

[miah alom]

Why dont you try it first then reply. I have already tested it and it works fine. I think you need some lesson on Group By.

[Anonymous]

well the query by alam_pune looks fine to me as well..... its a nice and simple sql and works fine too.....

[Anonymous]

select * from [table] where ID in (select min(ID) from [table] group by company )