Solved

SQL Query

Posted on 2007-11-30
6
445 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

626 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