Aggregate First Not Available?

The following query runs on Access and returns a result table that removes
duplicate account numbers even if they have different names.
SELECT AcctNum, First(t.AccountName) AS AccountName
FROM tblAcctChart t
GROUP BY AcctNum
ORDER BY AcctNum;
101 N1
101 N2
101 N3
results in
101 N1
I am feeling real dumb about this. First is not an aggregate function
available on SQL Server 2000. However, I know a way to accomplish the same
thing exists on SQL Server. I am having a serious senior moment and cannot
work my way through the fog to put one together.
Would some kind soul please give me a query that will run on SQL Server
2000 that will accomplish the same task.
Thanks.
MikeThere is no native ordering of rows in a table, so the meaning of "first"
depends on what criteria you use to pick to one AccountName that survives.
To pick the first name alphabetically:
SELECT AcctNum, MIN(AccountName) AS AccountName
FROM tblAccChart
GROUP BY AcctNum
ORDER BY AccNum
"MikeV06" wrote:

> The following query runs on Access and returns a result table that removes
> duplicate account numbers even if they have different names.
> SELECT AcctNum, First(t.AccountName) AS AccountName
> FROM tblAcctChart t
> GROUP BY AcctNum
> ORDER BY AcctNum;
> 101 N1
> 101 N2
> 101 N3
> results in
> 101 N1
> I am feeling real dumb about this. First is not an aggregate function
> available on SQL Server 2000. However, I know a way to accomplish the same
> thing exists on SQL Server. I am having a serious senior moment and cannot
> work my way through the fog to put one together.
> Would some kind soul please give me a query that will run on SQL Server
> 2000 that will accomplish the same task.
> Thanks.
> Mike
>|||You either need to use MIN or TOP, depending on what you want.
MIN(t.AccountName) will give you the first account name, alphabetically for
each AcctNum.
select top 1 from columnList
order by columnList
will return only the first row based on your order by clause.
"MikeV06" <me@.privacy.net> wrote in message
news:1t6yt3o7yvb6w.dlg@.mycomputer06.invalid.com...
> The following query runs on Access and returns a result table that removes
> duplicate account numbers even if they have different names.
> SELECT AcctNum, First(t.AccountName) AS AccountName
> FROM tblAcctChart t
> GROUP BY AcctNum
> ORDER BY AcctNum;
> 101 N1
> 101 N2
> 101 N3
> results in
> 101 N1
> I am feeling real dumb about this. First is not an aggregate function
> available on SQL Server 2000. However, I know a way to accomplish the same
> thing exists on SQL Server. I am having a serious senior moment and cannot
> work my way through the fog to put one together.
> Would some kind soul please give me a query that will run on SQL Server
> 2000 that will accomplish the same task.
> Thanks.
> Mike|||MikeV06 wrote:
> The following query runs on Access and returns a result table that removes
> duplicate account numbers even if they have different names.
> SELECT AcctNum, First(t.AccountName) AS AccountName
> FROM tblAcctChart t
> GROUP BY AcctNum
> ORDER BY AcctNum;
> 101 N1
> 101 N2
> 101 N3
> results in
> 101 N1
> I am feeling real dumb about this. First is not an aggregate function
> available on SQL Server 2000. However, I know a way to accomplish the same
> thing exists on SQL Server. I am having a serious senior moment and cannot
> work my way through the fog to put one together.
> Would some kind soul please give me a query that will run on SQL Server
> 2000 that will accomplish the same task.
> Thanks.
> Mike
Use MIN or MAX or come up with a better definition of what you mean by
"first". The Access query you posted actually returns a random result
for the value of AccountName, which may not be a good idea in many
cases.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> I am feeling real dumb about this. First is not an aggregate function
> available on SQL Server 2000.
Because "FIRST" does not make sense. First what? Physical row? Tables
are, by definition, an unordered set of rows. So the concepts of FIRST,
LAST, or 23rd row make absolutely no sense unless you give more context. As
others have noted, you can order alphabetically by using the aggregate
function MIN(t.AccountName).
I have a brief bit on this here (about 80% of the way down), but in general
the article may be useful to you as well:
http://www.aspfaq.com/2214
A|||My thanks to all who replied. Of course, Min/Max -- blind spot zapping me
broadside. I was overlooking the obvious and trying to make it difficult.
Arggg... Thanks again.
On Wed, 17 May 2006 13:46:01 -0700, Mark Williams wrote:
> There is no native ordering of rows in a table, so the meaning of "first"
> depends on what criteria you use to pick to one AccountName that survives.
> To pick the first name alphabetically:
> SELECT AcctNum, MIN(AccountName) AS AccountName
> FROM tblAccChart
> GROUP BY AcctNum
> ORDER BY AccNum
> "MikeV06" wrote:
>|||On Wed, 17 May 2006 17:05:46 -0400, Aaron Bertrand [SQL Server MVP] wrote:

> Because "FIRST" does not make sense. First what? Physical row? Tables
> are, by definition, an unordered set of rows. So the concepts of FIRST,
> LAST, or 23rd row make absolutely no sense unless you give more context.
As
> others have noted, you can order alphabetically by using the aggregate
> function MIN(t.AccountName).
> I have a brief bit on this here (about 80% of the way down), but in genera
l
> the article may be useful to you as well:
> http://www.aspfaq.com/2214
> A
Very useful indeed. Thank you for the url.