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

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

Re: Help with Query

Show all headers | View raw

On Aug 19, 5:52 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> FCB (dave.whit...@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, esq...@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

Erland,

Thanks very much for your help.

Dave

Back to comp.databases.ms-sqlserver | Previous | Next — Previous 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