From: esquel@sommarskog.se   
      
   Billy (void@pointer.co.uk) writes:   
   > I have a table whose structure is similar to this.   
   >   
   > ID bigint IDENTITY,   
   > Unit_ID bigint,   
   > Counter bigint   
   >   
   > The ID column is the PK column and the Unit_ID column will contain a FK   
   > value e.g. 12, the table will contain for example 300 rows with a   
   > Unit_ID of 12 and the Counter column will contain values in the range of   
   > 1 to 50 giving 6 sets of data.   
   >   
   > What I want to do is pull the IDs of the rows with the MIN(Counter) (as   
   > I can't guarantee it will be 1) value and a Unit_ID = 12, so I end up   
   > with 6 rows.   
   >   
   > Is this possible? I'm beginning to think I'm, wasting my time.   
      
   If I understand this correctly:   
      
   WITH ranked AS (   
      SELECT ID, Unit_ID, Counter,   
             rank = rank() OVER(ORDER BY Counter)   
      FROM  tbl   
      WHERE Unit_ID = 12   
   )   
   SELECT ID, Unit_ID, Counter   
   WHERE  rank = 1   
      
   This solution requires SQL 2005.   
      
   --   
   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)