First 3 Records

  • Thread starter Thread starter ChrisR
  • Start date Start date
C

ChrisR

I have a table of Vendors and contacts for the vendor. Count of contacts
per vendor can range from 1 to as many as 10+. I know that I can do a
query where I group by Vendor and take First of Contact Name. How can I get
First 3 of Contact Name.

So far my only idea is to do First Contact Name put aside and exclude from
list then do First Contract Name again. Repeat a 3rd time, then merge the 3
in a Union.

Any better ideas would be appreciated.

c-
 
You might be able to use a coordinated subquery with a Top 3 clause. It depends
on your table structure and table names.

Making assumptions about table names and field names, the following is a sample query.

SELECT V.VendorID, V.VendorName, C.ContactName
FROM Vendors as V INNER JOIN Contacts as C
ON V.VendorID = C.VendorID
WHERE C.ContactID in
(SELECT TOP 3 T.ContactID
FROM Contacts as T
WHERE T.VendorID = C.VendorID
ORDER BY T.ContactID)
 
Do you have a Table Vendors and a Table Contacts in a One-to-Many
relationship?

Post relevant details of your Table Structure.
 
Actually, it would just be 1 table with 2 fields. First field is Vendor and
second is Contact Name.

Was thinking maybe a select query of unique Vendor. Then join to that and
group by Vendor take top 3 ContactName?

Table would look like this...

Vendor ContactName
Honeywell Chris
Honeywell Tim
Honeywell John
Honeywell Mike
Honeywell Jason
APC Alex
APC Julie
Bussmann Susan
Bussmann Keith
Bussmann Kevin


but with > 100,000 records.
 
John,

Your idea worked great. I created a unique vendor list in a select
statement and used that as you V table and the main listing as the C table
and it worked like a charm.

Thanks for the help.

c-
 
Back
Top