asked on

SQL Query

I have a table that logs Archive/Restore transactions. I'm need help creating a query that will give
me a list showing one record per machine w/ the last transaction that occurred for that machine. For example,
from the data below, Machine3 would show up once with the 'Archive' transaction on 'Nov 29 2007 10:32AM'

Device TransType TransDate
-------- --------- ------------------
Machine1 ARCHIVE Nov 29 2007 12:13PM
Machine2 ARCHIVE Nov 29 2007 11:58AM
Machine3 ARCHIVE Nov 29 2007 10:32AM
Machine3 RESTORE Nov 29 2007 10:31AM
Machine4 ARCHIVE Nov 29 2007 6:26PM
Machine2 ARCHIVE Nov 29 2007 1:49PM
Machine3 RESTORE Nov 28 2007 5:20PM
Machine3 RESTORE Nov 28 2007 5:19PM
Machine3 ARCHIVE Nov 28 2007 5:19PM
Machine3 ARCHIVE Nov 28 2007 4:01PM
Machine3 RESTORE Nov 28 2007 4:01PM

The results would look like this:

Device TransType TransDate
-------- --------- ------------------
Machine1 ARCHIVE Nov 29 2007 12:13PM
Machine2 ARCHIVE Nov 29 2007 11:58AM
Machine3 ARCHIVE Nov 29 2007 10:32AM
Machine4 ARCHIVE Nov 29 2007 6:26PM

cmhunty

Does this work?

SELECT Device, TransType, MAX(TransDate) GROUP BY Device, TransType WHERE TransType = 'ARCHIVE'

cmhunty

Nope, that wouldn't but this might!!

SELECT Device, TransType, MAX(TransDate) FROM Table WHERE TransType = 'ARCHIVE' GROUP BY Device, TransType

Sorry, that first one was a shocker!

ASKER CERTIFIED SOLUTION

Richard Quadling

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cmhunty

Sorry, I totally misread the question

Richard Quadling

Hopefully your datetime column is a full date time column, so the chance of 2 actions occurring at exactly the same time is negated.

ndwHombre

ASKER

Thanks!!!!!!!!!