Top 3 with Group by 2

MapMan · Mar 15, 2000

Here's my delemna: 
 
I need to grab no more than three records from a table per company in a table that meets a certain criteria. 
 
Here's a table to use for this question. 
 
((I can't figure out how to get this to show in a formatted output, sorry)) 
 
CONTACT 
NAME TITLE COMPANY 
-------------------------- ----------------- --------------------- 
JOHN SMITH PRESIDENT DETROIT MOTORS, INC. 
DAVID BUEHLER VICE-PRESIDENT DETROIT MOTORS, INC. 
BOB WHEELER CHAIRMAN DETROIT MOTORS, INC. 
STAN RECORD VICE-PRESIDENT DETROIT MOTORS, INC. 
CATHY PAGE CHAIRMAN BOOK INN 
DANIEL TARIN PRESIDENT BOOK INN 
ERIC LEAVENWORTH VICE PRESIDENT BOOK INN 
RICHARD BURNHAM CIO BOOK INN 
… 
DANIEL PELTON CEO BOOK INN 
 
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. 
 
Here's what I've got but, it's not working. 
 
select C.Name, C.Title, C.Company from Contact C 
where Exists (Select * from contact 
where ( title like "%pres%" or 
title like "%CIO%" or 
title like "%CEO" or 
title like "%CHAIR%&quot

Group by Company) 
 
 
Any ideas? 
 
MapMan

calahans · Mar 16, 2000

What RDBMS are you using? 
 
IN SQL Server there is a TOP keyword, so you would use: 
 
select TOP 3 C.Name, C.Title, C.Company from Contact C 
where Exists (Select * from contact 
where ( title like "%pres%" or 
title like "%CIO%" or 
title like "%CEO" or 
title like "%CHAIR%&quot

Group by Company) 
 
Cal

MikeLacey · Mar 16, 2000

and in oracle there's a hidden column in every table returned by sql called rownum - so you'd just add a where clause "and rownum <= 3" 
 
-ml 
Mike Lacey <a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a> <a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a>

MapMan · Mar 16, 2000

I've tried calahans method. But that only brings up 3 records. 
 
What I'm trying to do is get up to three records per company in CONTACT. If my table has 50 records but only two companies, I am looking for 6 or fewer records overall. 
 
Is that even possible?

carp · Mar 16, 2000

MapMan - 
Try a correlated subquery: 
 
SELECT outer.Name, outer.Title, outer.company 
FROM contact outer 
WHERE outer.rowid IN 
(SELECT inner.rowid FROM contact inner 
WHERE outer.company = inner.company 
AND rownum < 4) 
ORDER BY outer.company; 
 
This will get you at most three contacts per company. You will have to expand on the subquery if there is a requirement for three specific contacts.

MapMan · Mar 17, 2000

Carp -- 
 
Now I know why I'd rather be using Oracle -vs- MSS 7.0. The latter, from what I can tell, doesn't have the rownum function.

I'm still working on it and really appreciate your help guys. 
 
Thanks, 
 
MapMan

carp · Mar 17, 2000

Based on calahans post you might be able to do the same thing like this: 
 
 
 
SELECT outer.Name, outer.Title, outer.company 
FROM contact outer 
WHERE outer.name IN 
(SELECT TOP 3 inner.name FROM contact inner 
WHERE outer.company = inner.company 
) 
ORDER BY outer.company; 
 
This avoids relying on rowid or rownum and uses the TOP function in sql server.

MapMan · Mar 17, 2000

Perfect!!!! 
 
This was right on the money and I can now get some sleep tonight. 
 
Thank you, 
 
MapMan

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Top 3 with Group by 2

MapMan

Programmer

calahans

Programmer

MikeLacey

MIS

MapMan

Programmer

carp

MIS

MapMan

Programmer

carp

MIS

MapMan

Programmer

Similar threads

Part and Inventory Search

Sponsor