(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 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
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
--
Thanks for the help, it works great. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
| |