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
ndwHombreAsked:
Who is Participating?
 
Richard QuadlingConnect With a Mentor Senior Software DeveloperCommented:
I assume the results should REALLY be ...

Machine1  ARCHIVE       Nov 29 2007 12:13PM
Machine2  ARCHIVE       Nov 29 2007  1:49PM <<<< 
Machine3  ARCHIVE       Nov 29 2007 10:32AM
Machine4  ARCHIVE       Nov 29 2007  6:26PM

SELECT
	Device,
	TransType,
	TransDate
FROM
	Table INNER JOIN
		(
		SELECT
			Device,
			MAX(TransDate) AS TransDate
		FROM
			Table
		GROUP BY
			Device
		) SubTable
			ON
				Table.Device = SubTable.Device
				AND
				Table.TransDate = SubTotal.TransDate
ORDER BY
	Device,
	TransType,
	TransDate

Open in new window

0
 
cmhuntyCommented:
Does this work?

SELECT Device, TransType, MAX(TransDate) GROUP BY Device, TransType WHERE TransType = 'ARCHIVE'
0
 
cmhuntyCommented:
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!
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
cmhuntyCommented:
Sorry, I totally misread the question
0
 
Richard QuadlingSenior Software DeveloperCommented:
Hopefully your datetime column is a full date time column, so the chance of 2 actions occurring at exactly the same time is negated.
0
 
ndwHombreAuthor Commented:
Thanks!!!!!!!!!
0
All Courses

From novice to tech pro — start learning today.