Top 3 with Group by

[MapMan]

((This question has been copied from the SQL forum))<br>
<br>
Here's my delemna:<br>
<br>
I need to grab no more than three records from a table per company in a table that meets a certain criteria.<br>
<br>
Here's a table to use for this question. <br>
<br>
((I can't figure out how to get this to show in a formatted output, sorry))<br>
<br>
CONTACT<br>
NAME TITLE COMPANY<br>
-------------------------- ----------------- ---------------------<br>
JOHN SMITH PRESIDENT DETROIT MOTORS, INC.<br>
DAVID BUEHLER VICE-PRESIDENT DETROIT MOTORS, INC.<br>
BOB WHEELER CHAIRMAN DETROIT MOTORS, INC.<br>
STAN RECORD VICE-PRESIDENT DETROIT MOTORS, INC.<br>
CATHY PAGE CHAIRMAN BOOK INN<br>
DANIEL TARIN PRESIDENT BOOK INN<br>
ERIC LEAVENWORTH VICE PRESIDENT BOOK INN<br>
RICHARD BURNHAM CIO BOOK INN<br>
…<br>
DANIEL PELTON CEO BOOK INN<br>
<br>
I need to write a query from the CONTACT table that will show no more than 3 contacts per company that meet the chief contact criteria.<br>
<br>
Here's what I've got but, it's not working.<br>
<br>
select TOP 3 C.Name, C.Title, C.Company from Contact C<br>
where Exists (Select * from contact <br>
where ( title like &quot;%pres%&quot; or<br>
title like &quot;%CIO%&quot; or<br>
title like &quot;%CEO&quot; or<br>
title like &quot;%CHAIR%&quot<br>
Group by Company)<br>
<br>
It's only returning the top 3 records. What I need is the top 3 per company.<br>
<br>
Any ideas?<br>
<br>

 

[phili5678]

try something like....

select C.Name, C.Title, C.Company from Contact C
where (select count(*) from Contact D where c.SOMEID=d.SOMEID and title like &quot;%pres%&quot; or
title like &quot;%CIO%&quot; or
title like &quot;%CEO&quot; or
title like &quot;%CHAIR%&quot <=3

if u need to group by then try...
select * from (select C.Name, C.Title, C.Company from Contact C
where (select count(*) from Contact D where c.SOMEID=d.SOMEID and title like &quot;%pres%&quot; or
title like &quot;%CIO%&quot; or
title like &quot;%CEO&quot; or
title like &quot;%CHAIR%&quot <=3) group by Company

hope this helps....

 

[rt63]

Let us say you want top 3 based on name. Try this


select a.Company, a.Name , max(a.title)
from Contact a , Contact b
where a.Company = b.Company
and a.Name >= b.Name
and ( a.title like &quot;%pres%&quot; or a.title like &quot;%CIO%&quot; or a.title like &quot;%CEO&quot; or a.title like &quot;%CHAIR%&quot
and ( b.title like &quot;%pres%&quot; or b.title like &quot;%CIO%&quot; or b.title like &quot;%CEO&quot; or b.title like &quot;%CHAIR%&quot
group by a.Company, a.Name
having count(a.Name) <= 3

RT