473,320 Members | 2,161 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Last Entry in a Log

(I don't post here often, so in case I'm violating long-standing taboos
of this newsgroup, I apologize in advance for calling a relation a
table, using nulls, and other ignorant, destructive, and comtemptible
terminology.)

I have a table that's keeping a sort of running log of different types
of changes to pieces of data. The table has a foreign key of the data
being changed, the foreign key for the type of change occuring, some
information about the change in a couple more columns, and a timestamp
for each entry. So it's:

dataID
eventID
eventInfo
timestamp

What I'd like to do, if at all possible, is a single SQL query that,
given a dataID, returns the most recent eventInfo and timestamp for
each eventID. Is this possible?

Many thanks.
-Eric

Aug 13 '05 #1
3 1443
(er*****@gmail.com) writes:
I have a table that's keeping a sort of running log of different types
of changes to pieces of data. The table has a foreign key of the data
being changed, the foreign key for the type of change occuring, some
information about the change in a couple more columns, and a timestamp
for each entry. So it's:

dataID
eventID
eventInfo
timestamp

What I'd like to do, if at all possible, is a single SQL query that,
given a dataID, returns the most recent eventInfo and timestamp for
each eventID. Is this possible?


SELECT a.eventID, a.eventInfo, a.timestamp
FROM tbl a
JOIN (SELECT eventID, timestamp = MAX(timestamp)
FROM tbl b
WHERE dataID = @dataid)
GRUOP BY eventID) AS b ON a.eventID = b.eventID
AND a.timestamp = b.timestamp
WHERE a.dataID = @dataid
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 13 '05 #2
Is your "timestamp" column actually a DATETIME or SMALLDATETIME column? If
so, try the following query. Don't use the name "timestamp", which refers to
a different datatype in SQL Server. The TIMESTAMP datatype has nothing to do
with date and time so if your column is in fact a TIMESTAMP then you ought
to add a DATETIME column instead.

SELECT eventid, eventinfo, timestamp
FROM YourTable AS T
WHERE timestamp =
(SELECT MAX(timestamp)
FROM YourTable
WHERE eventid = T. eventid
AND dataid = @dataid)
AND dataid = @dataid ;

--
David Portas
SQL Server MVP
--
Aug 13 '05 #3
Thanks for the help, it works great.

Aug 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Dan Matthews | last post by:
Hey everybody, I have an 2000 Access database that stores job listings from potential employers for a school. I would like to be able to display the date on a webpage the last time the database...
15
by: Ridimz | last post by:
When use ifstream, how do I ignore the last line of a file if it doesn't contain any information? Thanks in advance, Ridimz
0
by: gsb | last post by:
I'm not very good in mySQL but am trying to modify a script. It has to do with a log table. I want to retrieve the last 2 log records (if any) and if the last log entries are the same as my...
11
by: Vani Murarka | last post by:
Hi Everyone, Does .NET offer any collection class which will give me objects last *accessed* such that I may build a least-recently-used cache that kills off objects that haven't been used for...
2
by: PC | last post by:
Hi, I have used the DLast("!","")function to find and display the last entry in a table. How would I go about displaying the last record in a table that is not Null? Thanks in advance ...pc
11
by: David Messner | last post by:
Ok I know this is simple but the statement eludes me... I have a date field where I want the default value on the data entry form's date field to be the last date entered. I figure I can do this...
5
by: John Veldthuis | last post by:
My code works perfectly 100% when adding items to my ArrayList and updating the listbox. Works perfectly when deleting an item in the ArrayList when it is not the last entry but if it is the last...
2
by: Tony Ciconte | last post by:
Does anyone know of or have any VBA code or similar logic that can help distinguish similar first/last name combinations? For example, we would like to prompt the user of a possible match when any...
6
by: magix | last post by:
Hi, when I read entries in file i.e text file, how can I determine the first line and the last line ? I know the first line of entry can be filtered using counter, but how about the last line...
16
by: vjayis | last post by:
hi i have a xml file which has transaction details of the users. I m inserting data using appendChild() from php. While displaying i need to display the last 5 entry or single entry or the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.