Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From: Erland Sommarskog <esquel@sommarskog.se>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Help with Query
Date: Fri, 19 Aug 2011 23:52:22 +0200
Organization: Erland Sommarskog
Lines: 43
Message-ID: <Xns9F46F2D91B55FYazorman@127.0.0.1>
References: <9cb19b3e-a83f-4860-84d3-a82f12eadff7@b9g2000prd.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: mx04.eternal-september.org; posting-host="DD6dU+BfJNjsjSP4/K/V7w"; logging-data="30851"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX180vZhIgvdWrP9XhzWpy8sX"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:4ncPz+obI0dW/ZmFcB8g765cNtY=
Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:607

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