Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #607

Re: Help with Query

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Help with Query FCB <dave.white19@gmail.com> - 2011-08-19 13:51 -0700
  Re: Help with Query Erland Sommarskog <esquel@sommarskog.se> - 2011-08-19 23:52 +0200
    Re: Help with Query FCB <dave.white19@gmail.com> - 2011-08-20 09:19 -0700

csiph-web