d6ecf81d   
   From: esquel@sommarskog.se   
      
   FCB (dave.white19@gmail.com) writes:   
   > For example   
   >   
   > Account     Customer      First      Last                 Status   
   > 1               1                  James   Joyce   
   > Active   
   > 1               2                  Nora      Barnacle          Active   
   > 2               1                  Fred      Flintstone         Active   
   > 3               1                  Barney  Rubble   
   > Inactive   
   > 3               2                  Betty     Rubble             Active   
   >   
   > I'm trying to create a query that will choose the first active   
   > customer only. Is there a way to use the min() function to loop   
   > through each account number and choose the lowest customer per   
   > account? Or is there another function that can be used.   
      
   WITH numbered AS (   
       SELECT Account, First, Last, Status,   
              rowno = row_number()   
                       OVER (PARTITION BY Account ORDER BY Customer)   
       FROM   tbl   
   )   
   SELECT Account, First, Last, Status   
   FROM   tbl   
   WHERE  rowno = 1   
      
   The row_number() function orders the rows in the query, starting on one   
   for each new value in the PARTITION BY clause. The WITH thing is a   
   CTE, Common Table Expression, which is kind of view that exists only   
   for the query.   
      
   By getting the rows with row number = 1, you can easily get the first   
   row and all that goes with it.   
      
      
   --   
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)