Solved

SQL Query

Posted on 2007-11-30
6
413 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:
RQuadling 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 11

Expert Comment

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

Expert Comment

by:RQuadling
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

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.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now