Solved

SQL Query

Posted on 2007-11-30
6
432 Views
Last Modified: 2013-12-07
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
0
Comment
Question by:ndwHombre
  • 3
  • 2
6 Comments
 
LVL 11

Expert Comment

by:cmhunty
ID: 20383067
Does this work?

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

Expert Comment

by:cmhunty
ID: 20383075
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
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 400 total points
ID: 20383156
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 11

Expert Comment

by:cmhunty
ID: 20383224
Sorry, I totally misread the question
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 20383294
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
 

Author Closing Comment

by:ndwHombre
ID: 31411936
Thanks!!!!!!!!!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to recover a database from a user managed backup
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question