Last() Function?

Greetings,
I'm having some trouble to create a report. In this report I'm supposed to get the last row for each group of customers, ordered by date. Let me explain you a bit further using an example:
The table contains these fields, amongst others: CustomerID, Car, LastVisit.
So, for each customer there are many rows, each one with a different value in LastVisit. What I need to get, and before I started it looked quite easy, is a recordset with the last visit of each customer, given a specific car. For example, the last visit of each customer that has a Ford.
Now, I think there's a function LAST() in Access that actually gets the last row in a group by. That would be great, because the only thing I would have to do is grouping by CustomerID and getting the said last row, but unfortunatelly looks like SQL Server 2000 doesn't support that function.
The question is, how can I get that last row in each group? I've already tried using cursors in a stored procedure and temporary tables but I'm afraid I need a less time consuming solution as I'm dealing with more than 1.5 million rows.
If that's not possible, I guess I could "mark" the rows that I need and filter it later, but I'm not sure how to do it either...
If anyone around can give me some advice I would really appreciate it. I apologize if I was not very clear. I'll try to clarify it if you have doubts.
Thanks in advance,
Ricardo Lopez.

Two thoughts:

1. Try using MIN and MAX (whichever gives the correct value in this case) it will work on values which are non-numeric aswell.

2. Otherwise, use top with an order by, and use desc or asc as appropriate, and write a series of sql statments to get each group, and then combine using the UNION ALL operator.

|||

This will get you the results. It is using a derived table to get the last visit date for a customer id and then joining it with the same table to get the car of that visit.

select v.CustomerID, v.Car
from Visits v
inner join (select CustomerID as CustomerID, max(LastVisit) as LastVisit
from Visits
group by CustomerID) vlast
on (vlast.CustomerID = v.CustomerID and vlast.LastVisit = v.LastVisit)


I have assumed that LastVisit is a datetime column.

Last record in each group

Greetings anyone -

I have been attempting to figure out - in Report Builder - how to print only the last record in each group, i.e. the last activity. I see no Last function available anywhere.

Would someone please be so kind as to advise how this can be done?

If this cannot be accomplished in Rpt Builder, it looks as if the Last function is available within Report Designer. Unfortunately, although documentation states it's available, it does not state where this function can be utilized. I'm certainly having no luck.

Help pls?

Tks & B/R

I don't know how to do that in Report Builder, but in Report Designer you can use the Last Function in any expression. In the Edit Expression dialog, click on Common Functions, and then Last to insert fhe function into the expression.

Large Transacation Log even with Simple Recovery

Greetings,
I am running into a problem where some of our tables are
creating the log files to grow even with simple
recovery. Everything I read says that simple recovery
will prevent this. We especially notice this when we
delete the rows in a large table from the query analyzer
however if we issue the same command within a DTS package
the logs doesn't grow.
Any suggestions? Thanks!!hi terry,
using simple recovery model does not mean, DML operation is not logged. when
you issue a delete statement againtst the table it is logged into
transaction log, All that simple recovery mode does is, it truncates the log
each time SQL Server performs a checkpoint.
if you want to perform a large delete do it in chunks of batches, following
example will delete the rows from table in batch of 1000 per batch. Im not
sure what does DTS do behind the scene. probably, it is doing the same
thing. you can run a profiler trace and check whats happening.
ex:
set rowcount 1000
while 1=1
begin
delete from customers
if @.@.rowcount=0
break
end
set rowcount 0
Refer to following urls for more information.
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Transaction Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||Thank you for this information. It is very helpful!

>--Original Message--
>hi terry,
>using simple recovery model does not mean, DML operation
is not logged. when
>you issue a delete statement againtst the table it is
logged into
>transaction log, All that simple recovery mode does is,
it truncates the log
>each time SQL Server performs a checkpoint.
>if you want to perform a large delete do it in chunks of
batches, following
>example will delete the rows from table in batch of 1000
per batch. Im not
>sure what does DTS do behind the scene. probably, it is
doing the same
>thing. you can run a profiler trace and check whats
happening.
>ex:
>set rowcount 1000
>while 1=1
>begin
>delete from customers
>if @.@.rowcount=0
>break
>end
>set rowcount 0
>Refer to following urls for more information.
>http://www.support.microsoft.com/?id=256650 INF: How
to Shrink the SQL
>Server 7.0 Transaction Log
>http://www.support.microsoft.com/?id=317375 Log File
Grows too big
>http://www.support.microsoft.com/?id=110139 Log file
filling up
>http://www.mssqlserver.com/faq/logs-shrinklog.asp
Shrink File
>http://www.support.microsoft.com/?id=315512
Considerations for Autogrow
>and AutoShrink
>http://www.support.microsoft.com/?id=272318 INF:
Shrinking Log in SQL
>Server 2000 with DBCC SHRINKFILE
>
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>
>.
>

Large Transacation Log even with Simple Recovery

Greetings,
I am running into a problem where some of our tables are
creating the log files to grow even with simple
recovery. Everything I read says that simple recovery
will prevent this. We especially notice this when we
delete the rows in a large table from the query analyzer
however if we issue the same command within a DTS package
the logs doesn't grow.
Any suggestions? Thanks!!
hi terry,
using simple recovery model does not mean, DML operation is not logged. when
you issue a delete statement againtst the table it is logged into
transaction log, All that simple recovery mode does is, it truncates the log
each time SQL Server performs a checkpoint.
if you want to perform a large delete do it in chunks of batches, following
example will delete the rows from table in batch of 1000 per batch. Im not
sure what does DTS do behind the scene. probably, it is doing the same
thing. you can run a profiler trace and check whats happening.
ex:
set rowcount 1000
while 1=1
begin
delete from customers
if @.@.rowcount=0
break
end
set rowcount 0
Refer to following urls for more information.
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Transaction Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Thank you for this information. It is very helpful!

>--Original Message--
>hi terry,
>using simple recovery model does not mean, DML operation
is not logged. when
>you issue a delete statement againtst the table it is
logged into
>transaction log, All that simple recovery mode does is,
it truncates the log
>each time SQL Server performs a checkpoint.
>if you want to perform a large delete do it in chunks of
batches, following
>example will delete the rows from table in batch of 1000
per batch. Im not
>sure what does DTS do behind the scene. probably, it is
doing the same
>thing. you can run a profiler trace and check whats
happening.
>ex:
>set rowcount 1000
>while 1=1
>begin
>delete from customers
>if @.@.rowcount=0
>break
>end
>set rowcount 0
>Refer to following urls for more information.
>http://www.support.microsoft.com/?id=256650 INF: How
to Shrink the SQL
>Server 7.0 Transaction Log
>http://www.support.microsoft.com/?id=317375 Log File
Grows too big
>http://www.support.microsoft.com/?id=110139 Log file
filling up
>http://www.mssqlserver.com/faq/logs-shrinklog.asp
Shrink File
>http://www.support.microsoft.com/?id=315512
Considerations for Autogrow
>and AutoShrink
>http://www.support.microsoft.com/?id=272318 INF:
Shrinking Log in SQL
>Server 2000 with DBCC SHRINKFILE
>
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>
>.
>