Group by - last/first - Jet/Sql server 7.0

[Alfonso Moscato]

Hi to all,
I am trying to move my Access application to an ADP. 95% works fine, of
course with some changes. But I can't figure how translate this in ADP/Sql
Server.
In a query I must group by a field, say "Call ID". For every call Id I can
have one or more records, and I need the values of the last record of the
group (by date). In access, I resolved with two queries: the first one
ordering records by date, and the second grouping by Call Id and using Last
as aggregate function for all the fields.
Now in Sql Server I can't find Last and first as aggregate functions, and I
can't figure how to obtain the same results. I think I must use a stored
procedure, but which is the rigth way ? Creating a temporary table and
returning it ? Using two nested stored procedures (and If so, how?) ? Or
something else ? And also which is the quickest way ?
Thanks in advance for any help.
Bye
Alfonso Moscato

[BurtonRoberts]

First and Last not supported in T-SQL. Use Max and Min if you have an
identity field (autonumber) in the table. Otherwise you're stuck, I think

"Alfonso Moscato" <mosca...@tin.it> wrote in message
news:u4ZkEB8pAHA.1480@tkmsftngp04...

[Michel Walsh]

Hi,

If you have only one field that is aggregated by Last, use min, or max. If
you have many fields, you can't use min or max:

F1, F2, F3
a, 1, 100
a, 2, 99

In Jet, SELECT F1, Last(F2), Last(F3) FROM ... GROUP BY F1 will return
either the first either the second record. Using:
SELECT F1, Min(F2), Min(F3) FROM .... will return

a, 1, 99

which is NOT a record from the initial set. So, if you aggregate with Last
on many fields, you have to use a sub-select, along the line:

SELECT A.F1, A.F2, A.F3
FROM Somewhere AS A
WHERE A.PrimaryKey =
(SELECT MIN(B.primaryKey)
FROM somewhere As B
WHERE A.F1=B.F1 )

which can be a translation in MS SQL Server for:

SELECT F1, Last(F2), Last(F3)
FROM Somewhere
GROUP BY F1

in Jet.

Hoping it may help,
Vanderghast, Access MVP

"Alfonso Moscato" <mosca...@tin.it> wrote in message
news:u4ZkEB8pAHA.1480@tkmsftngp04...

[Alfonso Moscato]

Thanks Michel.
Your solution is really interesting. My solution was to create a view with
SELECT A.F1,MAX(A.F2) as MAXB FROM A LEFT OUTER JOIN B ON A.F1 = B.F1 as C,
and then a new view with joins on C.F1 and A.F1 and on C.maxb and B.F2, but
maybe your idea is more performing. I will try it.
Bye
Alfonso
"Michel Walsh" <Vande...@msn.com> ha scritto nel messaggio
news:uknPShGsAHA.2100@tkmsftngp04...

> Hi,
>
>
> If you have only one field that is aggregated by Last, use min, or max. If
> you have many fields, you can't use min or max:
>
>
> F1, F2, F3
> a, 1, 100
> a, 2, 99
>
>
> In Jet, SELECT F1, Last(F2), Last(F3) FROM ... GROUP BY F1 will return
> either the first either the second record. Using:
> SELECT F1, Min(F2), Min(F3) FROM .... will return
>
> a, 1, 99
>
> which is NOT a record from the initial set. So, if you aggregate with Last
> on many fields, you have to use a sub-select, along the line:
>
>
> SELECT A.F1, A.F2, A.F3
> FROM Somewhere AS A
> WHERE A.PrimaryKey =
> (SELECT MIN(B.primaryKey)
> FROM somewhere As B
> WHERE A.F1=B.F1 )
>
>
> which can be a translation in MS SQL Server for:
>
> SELECT F1, Last(F2), Last(F3)
> FROM Somewhere
> GROUP BY F1

[...]