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.

Large Transaction Log Files - SQL 2000

The SQL 2000 transaction log backups for our customer's databases are
regularly quite large - often times almost as large as the database itself
despite the fact that we run transaction log backups nightly.
We're wondering if the size of the log backups is related to the SQL
optimization jobs we regularly run. How often is it appropriate to run an
optimization job? When is the best time to do the optimizations? (After a
full db backup? Before a full db backup? After a transaction log
backup?Before a transaction log backup?) Or does it really matter at all?
Our databases vary greatly in size from a few megabytes to a few gigabytes.
Being ecommerce website databases, a large percentage of the activity is
read activity (shoppers browsing the site) but there is write activity when
shoppers register, place an order etc. The number of transactions varies
widely from a transaction once a week to hundreds or thousands of
transactions per day depending on which customers database we are talking
about.
Thanks,
Brad
It sounds like a lot of the Transaction Log activity may be related to the
optimization efforts.
Why do you feel the need to exercise optimization efforts out of band with
the backup schedule?
Depending upon write activity, and types of indexes, padding, etc.,
optimization may be occurring much too often.
If you wanted to provide more details about your optimization and backup
schedules, we may be able to give you more directed assistance.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:uKE47DmEHHA.4024@.TK2MSFTNGP04.phx.gbl...
> The SQL 2000 transaction log backups for our customer's databases are
> regularly quite large - often times almost as large as the database itself
> despite the fact that we run transaction log backups nightly.
> We're wondering if the size of the log backups is related to the SQL
> optimization jobs we regularly run. How often is it appropriate to run an
> optimization job? When is the best time to do the optimizations? (After a
> full db backup? Before a full db backup? After a transaction log
> backup?Before a transaction log backup?) Or does it really matter at all?
> Our databases vary greatly in size from a few megabytes to a few
> gigabytes. Being ecommerce website databases, a large percentage of the
> activity is read activity (shoppers browsing the site) but there is write
> activity when shoppers register, place an order etc. The number of
> transactions varies widely from a transaction once a week to hundreds or
> thousands of transactions per day depending on which customers database we
> are talking about.
> Thanks,
> Brad
>
|||Thanks for the information. Here is how we have our SQL 2000 Maintenance
Plan Setup:
[Optimization]
[x] Reorganize data and index pages
( ) Reorganize pages with the original amount of free space
(*) Change Free Space per page percentage to: 10%
[ ] Update the statistics used by the query optimizer
Percentage of database to sample: (grayed out)
[ ] Remove unused space from the database files
( ) Shrink database when it grows beyond: (grayed out)
( ) Amount of free space to remain after shrink (grayed out)
Occurs every 1 week(s) on Sunday, at 1:00:00 AM.
[Integrity Check]
[x] Check database integrity
(*) Include indexes
[ ] Attempt to repair any minor problems
( ) Excluded indexes
[ ] Perform these tests before backing up the database or transaction log.
Occurs every 1 week(s) on Sunday, at 12:00:00 AM.
[Complete Backup]
[x] Backup the database as part of the maintenance plan
[x] Verify the integrity of the backup upon completion
( ) Tape (grayed out)
( ) Disk
( ) Use the default backup directory
(*) Use this directory (backup directory)
[x] Create a sub-directory for each database
[x] Remove files older than 2 weeks
Backup extension: BAK
Occurs every 1 week(s) on Sunday, at 2:00:00 AM
[Transaction Log Backup]
[x] Backup the transaction log of the database as part of the maintenance
plan
[x] Verify the integrity of the backup upon completion
( ) Tape (grayed out)
( ) Disk
( ) Use the default backup directory
(*) Use this directory (backup directory)
[x] Create a sub-directory for each database
[x] Remove files older than 2 weeks
Backup extension: TRN
Occurs every 1 day(s), every 4 hour(s) between 12:15:00 AM and 6:59:59 AM.
The reason for limiting transaction log backups to the night was that we
found they were negatively impacting website performance.
Hopefully that makes sense but please let me know if you have questions.
Thanks,
Brad
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uQNhHHmEHHA.4808@.TK2MSFTNGP03.phx.gbl...
> It sounds like a lot of the Transaction Log activity may be related to the
> optimization efforts.
> Why do you feel the need to exercise optimization efforts out of band with
> the backup schedule?
> Depending upon write activity, and types of indexes, padding, etc.,
> optimization may be occurring much too often.
> If you wanted to provide more details about your optimization and backup
> schedules, we may be able to give you more directed assistance.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "Brad Baker" <brad@.nospam.nospam> wrote in message
> news:uKE47DmEHHA.4024@.TK2MSFTNGP04.phx.gbl...
>
|||From your statement that TLog backups impact the website performance, it
sounds like the SQL Server is running on the same box as the Web Server AND
it must also be an 'underpowered' server. (I've often seen TLog backups on
multiGB databases having lots of activity take only seconds when done
hourly -and the users never realized anything was different.)
For your current schedule, I would make sure that there is NO TLog backup on
Sunday -it is most likely is happening before the optimization and FULL
Backup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:uhAuaUoEHHA.4024@.TK2MSFTNGP04.phx.gbl...
> Thanks for the information. Here is how we have our SQL 2000 Maintenance
> Plan Setup:
>
> [Optimization]
> [x] Reorganize data and index pages
> ( ) Reorganize pages with the original amount of free space
> (*) Change Free Space per page percentage to: 10%
>
> [ ] Update the statistics used by the query optimizer
> Percentage of database to sample: (grayed out)
>
> [ ] Remove unused space from the database files
> ( ) Shrink database when it grows beyond: (grayed out)
> ( ) Amount of free space to remain after shrink (grayed out)
>
> Occurs every 1 week(s) on Sunday, at 1:00:00 AM.
>
>
>
> [Integrity Check]
> [x] Check database integrity
> (*) Include indexes
> [ ] Attempt to repair any minor problems
> ( ) Excluded indexes
>
> [ ] Perform these tests before backing up the database or transaction log.
>
> Occurs every 1 week(s) on Sunday, at 12:00:00 AM.
>
>
>
> [Complete Backup]
> [x] Backup the database as part of the maintenance plan
> [x] Verify the integrity of the backup upon completion
>
> ( ) Tape (grayed out)
> ( ) Disk
> ( ) Use the default backup directory
> (*) Use this directory (backup directory)
>
> [x] Create a sub-directory for each database
> [x] Remove files older than 2 weeks
>
> Backup extension: BAK
>
> Occurs every 1 week(s) on Sunday, at 2:00:00 AM
>
>
> [Transaction Log Backup]
> [x] Backup the transaction log of the database as part of the maintenance
> plan
> [x] Verify the integrity of the backup upon completion
>
> ( ) Tape (grayed out)
> ( ) Disk
> ( ) Use the default backup directory
> (*) Use this directory (backup directory)
>
> [x] Create a sub-directory for each database
> [x] Remove files older than 2 weeks
>
> Backup extension: TRN
>
> Occurs every 1 day(s), every 4 hour(s) between 12:15:00 AM and 6:59:59 AM.
>
>
> The reason for limiting transaction log backups to the night was that we
> found they were negatively impacting website performance.
>
> Hopefully that makes sense but please let me know if you have questions.
>
> Thanks,
> Brad
>
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:uQNhHHmEHHA.4808@.TK2MSFTNGP03.phx.gbl...
>
|||Does you maintenance plan include more than one database?
I ask, because we are having the same problem and our maintenance plan
specifies 3 databases - where the 1st log file looks fine and the 2nd & 3rd
are quite large.
I just made a post on the subject.
|||We have 6 maintenance plans - all of which contain about 60 databases each.
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:EF1DFA32-E412-4A9D-850E-68783CF8BDA0@.microsoft.com...
> Does you maintenance plan include more than one database?
> I ask, because we are having the same problem and our maintenance plan
> specifies 3 databases - where the 1st log file looks fine and the 2nd &
> 3rd
> are quite large.
> I just made a post on the subject.
|||We have several DB servers all experiencing this problem. They are all
dedicated SQL servers though - no other applications running on them.
The servers are running dual xeon processors with 8GB of RAM. Some of the
servers have a handful of very large databases and other servers have a lot
(100-200) small databases. That's not to say the server isn't underpowered
for the load we are placing on it - sometimes the load is quite high and
other times its nominal.
When the Tlog backups were running we would see ASP errors involving SQL
timeouts as well as short disruptions in service.. so that's why we decided
we probably out to limit tlog backups to night.
I'm going to try disabling transaction log backups on Sunday and see if that
improves things. If you have any other ideas please let me know
Thanks,
Brad
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uuYyNXxEHHA.4508@.TK2MSFTNGP02.phx.gbl...
> From your statement that TLog backups impact the website performance, it
> sounds like the SQL Server is running on the same box as the Web Server
> AND it must also be an 'underpowered' server. (I've often seen TLog
> backups on multiGB databases having lots of activity take only seconds
> when done hourly -and the users never realized anything was different.)
> For your current schedule, I would make sure that there is NO TLog backup
> on Sunday -it is most likely is happening before the optimization and FULL
> Backup.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "Brad Baker" <brad@.nospam.nospam> wrote in message
> news:uhAuaUoEHHA.4024@.TK2MSFTNGP04.phx.gbl...
>