From: esquel@sommarskog.se   
      
   Plamen Ratchev (Plamen@SQLStudio.com) writes:   
   > It will be best to post sample data and the expected results.   
   >   
   > Try this:   
   >   
   > SELECT id, unit_id, counter   
   > FROM (   
   > SELECT id, unit_id, counter,   
   >         ROW_NUMBER() OVER(PARTITION BY unit_id ORDER BY counter) AS rk   
   > FROM Table) AS T   
   > WHERE rk <= 6;   
      
   Or maybe this is what Billy is looking for:   
      
    SELECT id, unit_id, counter   
    FROM (   
    SELECT id, unit_id, counter,   
            RANK() OVER(PARTITION BY unit_id ORDER BY counter) AS rk   
    FROM Table) AS T   
    WHERE rk = 1;   
      
   I think there are two ways that Billy can reach his goal. One is that   
   he studies the row_number, rank and dense_rank functions, working from   
   our examples.   
      
   The other is that he, as Plamen suggests, posts:   
      
   o   CREATE TABLE statements for his table.   
   o   INSERT statements with sample data.   
   o   The desired result given the sample.   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)