Query Help

  • Thread starter Thread starter fgwiii
  • Start date Start date
F

fgwiii

I am trying to create a query(s) that will return one
entry for each InvID where the value in ContactID is the
highest number. Based on the table in fig-1 below the
query would return the rows as in fig-2.

fig-1
ContactIDInvID Contact ContactTypeText
6 4 Jodie Nels Site Coordinator
7 4 Karen Lomb Site Coordinator
8 4 Jean Red Site Coordinator
9 5 Traci Shock Site Coordinator
10 5 Jenn Randall Site Coordinator
11 6 Kath Monihan Site Coordinator

fig-2
ContactIDInvID Contact ContactTypeText
8 4 Jean Red Site Coordinator
10 5 Jenn Randall Site Coordinator
11 6 Kath Monihan Site Coordinator

Thanks for your help

Fred
 
Fred,

You need a nested subquery that selects the max contactID
for each InvID, then links that back to your table.
Try:

SELECT ContactID, InvID, Contact, ContactTypeText
FROM yourTable T
INNER JOIN
(SELECT MAX(ContactID) as MaxContact, InvID
FROM yourTable
GROUP BY InvID) as T1
ON T.ContactID = T1.MaxContact
AND T.InvID = T1.InvID

HTH
Dale
 
Try a subquery that Identifies the max contactId for each InvId. Something like
the one below

SELECT ContactID, InvID, Contact, ContactTypeText
FROM YOURTable
WHERE ContactID IN
(SELECT Max(Tmp.ContactID)
FROM YourTable as Tmp
Group By Tmp.InvID)
 
Back
Top