Solved

SQL Query

Posted on 2007-11-30
6
441 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

737 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