title: Filtering of results returned by SQL query
body: <p>I've been struggling to work this out all afternoon - it seems pretty simple but I must be missing something!</p>
  
  <p>I've got a query which returns some data, two of the columns it returns are "PackageWeight" and "PackageGroup". Essentially, I want to filter this data down to show only one row for each "PackageGroup" - that should be the row with the highest value in the "PackageWeight" column.</p>
  
  <p>It seems simple but I just can't get it to work in SQL Server using a combination of TOP 1 and GROUP BY. I must be missing something!</p>
  
  <pre><code>    SELECT VendorID, PackageID, PackageWeight, PackageGroup
    FROM (SELECT VendorID, COUNT(*) AS qty
            FROM VendorServices
           GROUP BY VendorID
         ) cs
    JOIN (SELECT PackageServices.PackageID, lookupPackages.PackageWeight, lookupPackages.PackageGroup, COUNT(*) AS qty
            FROM PackageServices
            JOIN lookupPackages ON PackageServices.PackageID = lookupPackages.PackageID
            GROUP BY PackageServices.PackageID, lookupPackages.PackageWeight, lookupPackages.PackageGroup
         ) ps ON cs.qty &gt;= ps.qty
    WHERE (SELECT COUNT(*)
            FROM VendorServices cs2
            JOIN PackageServices ps2 ON cs2.ServiceTypeID = ps2.ServiceID
           WHERE cs2.VendorID = cs.VendorID
             AND ps2.PackageID = ps.PackageID
         ) = ps.qty
  </code></pre>
  
  <p>This query returns me the complete dataset, that I need to filter down. However my attempts so far have failed :(</p>
  
  <p>Any help much appreciated!</p>
  
  <p><strong>EDIT</strong> - Thanks to contributors below, so far I have the following query:</p>
  
  <pre><code>with result_cte as
  (
  SELECT VendorID, PackageID, PackageWeight, PackageGroup,
      RANK() over (partition by PackageGroup order by PackageWeight desc) as [rank]
  FROM (SELECT VendorID, COUNT(*) AS qty
      FROM VendorServices
      GROUP BY VendorID
      ) cs
  JOIN (SELECT PackageServices.PackageID, lookupPackages.PackageWeight, lookupPackages.PackageGroup, COUNT(*) AS qty
      FROM PackageServices
      JOIN lookupPackages ON PackageServices.PackageID = lookupPackages.PackageID
      GROUP BY PackageServices.PackageID, lookupPackages.PackageWeight, lookupPackages.PackageGroup
      ) ps ON cs.qty &gt;= ps.qty
  WHERE (SELECT COUNT(*)
      FROM VendorServices cs2
      JOIN PackageServices ps2 ON cs2.ServiceTypeID = ps2.ServiceID
      WHERE cs2.VendorID = cs.VendorID
      AND ps2.PackageID = ps.PackageID
      ) = ps.qty
  )
  
  select *
  from result_cte
  WHERE [rank] = 1
  ORDER BY VendorID
  </code></pre>
  
  <p>So far, so good. I'll still take a look at the APPLY operator suggested by @gbn as this is new to me - and I still need to do some testing to ensure that this query works 100% of the time. However initial indications are good!</p>
  
  <p>Thanks to all who have contributed so far.</p>
  
  <p><strong>EDIT 2</strong> - Sadly, after populating the database with more example data, this query failed to work. It seems to miss out some entries.</p>
  
  <p>Perhaps I need to explain a little more about what is going on here. The data being returned by my original query lists every customer in the system, along with the derived PackageID (calculated by that query) and the weight and group assigned to that Package in a lookup table.</p>
  
  <p>I need to filter the original results table so that I get no more than one package from each group, for each customer (each customer may have a package from one or more group but might not have a package from every group)</p>
  
  <p>I'll take a fresher look at this tomorrow as I think I might be in a 'Can't see the wood for the trees' situation! </p>
  
  <p>Thanks all.</p>