Have written basic queries with SQL for a while now but have been
given a project that requires me to step it up a notch. Any help would
be appreciated.

I have a table that lists customer records; one row per customer but
there can be more than one customer per account.

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.

Thanks again,

Dave

FCB (dave.white19 (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) 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

On Aug 19, 5:52*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Erland,

Thanks very much for your help.

Dave