Select Top N records

[Tech2377]

I have a table, productTable, that contains a ProductID column, a bunch of attribute columns like manufacturer etc, and a GroupID. Many different productIDs can share the same GroupID which means that they are basically the same product with one different attribute such as color. What I'm trying to do is select all the products in the TOP N GroupIDs. I'm using this to paginate my query results. I tried this which does not work:

SELECT DISTINCT TOP 10 *
FROM productTable
WHERE GroupID NOT IN
(SELECT DISTINCT TOP 10 GroupID
FROM productTable
ORDER BY GroupID)
ORDER BY GroupID

This returns the TOP 10 rows, but I would like all the ProductIDs for the TOP 10 GroupIDs.

 

[r937]

top 10 GroupIDs based on what? the number of products? or the GroupID itself? ascending or descending?

SELECT please
     , list
     , only
     , the
     , product
     , columns
     , you
     , really
     , need
  FROM productTable
 WHERE GroupID IN
       ( SELECT TOP 10 
                GroupID
           FROM productTable
         GROUP BY GroupID
         ORDER BY GroupID DESC )

r937.com | rudy.ca