TOP 1 Subquery Problem

[GregD]

Here's the query...

SELECT tblSJHSVendorCount.MembID, 
    tblSJHSVendorCount.Vendor, 
    (SELECT TOP 1 VendCount2.CountOfVendor
    FROM tblSJHSVendorCount AS VendCount2 
    WHERE  tblSJHSVendorCount.MembID = VendCount2.MembID 
    ORDER BY VendCount2.CountOfVendor DESC, Counter) AS MaxVendCount
    FROM tblSJHSVendorCount;

Each MembID as multiple vendors. Each vendor has a count associated with is. [Counter] is a unique id on the table. I want the MembID and the Vendor with the highest count. One row per member. It is not working. The table has 1470 rows and the query returns all rows.

Help!

Greg

 

[ByteMyzer]

Try:

SELECT T1.MembID, T1.Vendor, T1.CountOfVendor
FROM tblSJHSVendorCount T1
WHERE T1.Vendor =
(SELECT TOP 1 T2.Vendor
 FROM tblSJHSVendorCount T2
 WHERE T2.MembID = T1.MembID
 ORDER BY T2.CountOfVendor DESC);

 

[GregD]

Thanks, that got it. At least it was very close. I did need to add back in the [Counter] to the subquery because two of the members have two rows where both vendor counts were identical. The unique counter breaks the tie.

I see now what I was doing wrong. This was very helpful. Thanks.

 

[ByteMyzer]

Excellent. I'm glad you got it sorted out