Conversion of Access query using First() Aggregate

All,
I am trying to figure out how to convert this particular query, and am
stumped...
SELECT tCollatList.CollatID, tCollatList.ListCatID, tlListCategory.Category,
First(tlListCategory.[CatWholeVal%]) AS [FirstOfCatWholeVal%],
First(tlListCategory.Unit) AS FirstOfUnit, First(tlListCategory.[$Unit]) AS
[FirstOf$Unit], Sum([tCollatList]![ColListIncDec]) AS Balance,
[Balance]*[FirstOf$Unit] AS BalValue, [BalValue]*[FirstOfCatWholeVal%] AS
BalWhole
FROM tCollatList LEFT JOIN tlListCategory ON tCollatList.ListCatID =
tlListCategory.ListCatID
GROUP BY tCollatList.CollatID, tCollatList.ListCatID, tlListCategory.Categor
y
HAVING (((tCollatList.CollatID)=9));
SQL server doesn't support 'First(tlListCategory.[$Unit]) AS [FirstOf$Unit]'
Can someone please tell me what I can do, if anything to duplicate this
functionallity?
ThanksScottW wrote:
> All,
> I am trying to figure out how to convert this particular query, and am
> stumped...
> SELECT tCollatList.CollatID, tCollatList.ListCatID, tlListCategory.Categor
y,
> First(tlListCategory.[CatWholeVal%]) AS [FirstOfCatWholeVal%],
> First(tlListCategory.Unit) AS FirstOfUnit, First(tlListCategory.[$Unit]) A
S
> [FirstOf$Unit], Sum([tCollatList]![ColListIncDec]) AS Balance,
> [Balance]*[FirstOf$Unit] AS BalValue, [BalValue]*[FirstOfCatWholeVal%] AS
> BalWhole
> FROM tCollatList LEFT JOIN tlListCategory ON tCollatList.ListCatID =
> tlListCategory.ListCatID
> GROUP BY tCollatList.CollatID, tCollatList.ListCatID, tlListCategory.Categ
ory
> HAVING (((tCollatList.CollatID)=9));
> SQL server doesn't support 'First(tlListCategory.[$Unit]) AS [FirstOf$Unit
]'
> Can someone please tell me what I can do, if anything to duplicate this
> functionallity?
> Thanks
The problem is that FIRST is not a real aggregate function, even in
Access. Access's FIRST and LAST functions just return some arbitrary
value from the set of rows in question. So you might as well use MIN or
MAX instead. If you want a different answer then you need a better
specification of what you mean by "first" and "last". Tables have no
inherent order and nor do query results unless you use ORDER BY.
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
--|||Hello David,
The code provided was specific, using SQL Server 2000, specifically it
says First is an unknown keyword.
As for there being arbitrary values, not in this case. Each value in the
joined table is identical, which is why we need only one record, so the
values from the other table can be summed. I guess that being said, usinfg
min or max should return the desired results. Thoughts?
Thanks
"David Portas" wrote:

> ScottW wrote:
> The problem is that FIRST is not a real aggregate function, even in
> Access. Access's FIRST and LAST functions just return some arbitrary
> value from the set of rows in question. So you might as well use MIN or
> MAX instead. If you want a different answer then you need a better
> specification of what you mean by "first" and "last". Tables have no
> inherent order and nor do query results unless you use ORDER BY.
> --
> 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
> --
>|||ScottW wrote:
> Hello David,
> The code provided was specific, using SQL Server 2000, specifically it
> says First is an unknown keyword.
That's right.

> As for there being arbitrary values, not in this case. Each value in the
> joined table is identical, which is why we need only one record, so the
> values from the other table can be summed.
In that case you can just add CatWholeVal%, Unit and $Unit to the GROUP
BY list. Then you don't need to use an aggregate function at all.
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
--sqlsql