finding last record of each

[je27]

I have a table that contains a timestamp that is captured after each time a particular unique instance (example person) is changed or a transaction takes place on that person.
So for example if my table contains 30 different users that are logged in or logged out or absent or on vacation i want to find the last timestamp for each of those users and what the transaction was. ?
I tried this but with no lucK:

select * from transtable where timestamp in
(select max(timestamp) from transtable)
and userid in (.......)

 

[gmmastros]

try this...

select * 
from   transtable 
       Inner Join (
         select max(timestamp) As timeStamp, 
                UserId 
         from   transtable
         Group By UserId
         ) As A
         On transtable.userid = A.userid
         and transtable.timestamp = A.timestamp

If this works for you, and you don't understand it, then let me know and I will explain it for you.

-George

"the screen with the little boxes in the window." - Moron

 

[Mercury2]

   select max(timestamp) As MaxTimeStamp, UserId
         from   transtable
         where  userId in (...)
         Group By UserId

 

[je27]

Hi George,
I've tried to run the code you supplied and its giving me the following error:
ORA-0095 Missing keyword
on the line containing:

) As A

Also, is Inner Join a recognized sql function, because it seems only the Join is blue and the Inner isn't.

could i add another where clause after the last line incase I wanted to specify which userids i was interested in.
For example:
On transtable.userid = A.userid
and transtable.timestamp = A.timestamp
and transtable.userid in ('xtredf','sasdf')

 

[je27]

Hi mercury,
i tried your code as well and if i change it a bit to give me other information it will return all the max timestamps of each different transaction for that user.
So what I'm trying to get it to do now is return the last transaction for that user. ? hope that makes sense.
Thank you !

 

[je27]

Hi George,
I believe I figured out the syntax error in the code. I took out the AS from:
) As A
and it seemed to have worked fine. I also was able to put in my where clauses.
Now if you could go through a quick explanation that would be wonderful.

THanks again!

 

[gmmastros]

My apologies for the syntax issue. I am not familiar with Oracle, but am very familiar with SQL Server.

The question you ask is actually very common. I want all the data for each user where the timestamp is greatest.

Start by creating a query that shows you the last (max) timestamp for each user.

[tt][blue]Select UserId, Max(TimeStamp)
From Transtable
Group By UserId
[/blue][/tt]

This query will return 1 record for each userid, and also indicate what the Max(timestamp) is. The problem occurs when you want to return more data. Anything returned by this query must be an aggregate (Max, sum, avg, etc...) or be part of the group by.

One way to solve this problem is to do a derived table/self join. At times, you can think of an inner join as a filter condition. If all the join conditions are not met, then the record is filtered out. We will use this to our advantage here. In this case, the above query will only return 1 record for each user. The timestamp will be the max timestamp for the use. So, by joining on both conditions, we are effectively filtering out all of the other records where the timestamp is not the MAX Timestamp.

Since the outer query is not using an aggregate, we don't need to bother with a group by clause.

Does this make sense? If it doesn't, let me know and I will try to explain it some more. This is an important concept, so it's important that you understand it.




-George

"the screen with the little boxes in the window." - Moron