Home » SQL & PL/SQL » SQL & PL/SQL » retrieve last access time for users from table
retrieve last access time for users from table [message #211478] Thu, 28 December 2006 21:24 Go to next message
kanwaljitsingh
Messages: 2
Registered: December 2006
Junior Member
Being resonably new to pl/sql, and db guy on christmas holiday. I am stuck

I am using using Oracle & this log table stores all access request info users.
From this table, I am only interested in timestamp , username, category column.


I want to access latest access time for EACH user, on the lines below.
===================================================
SELECT USERNAME , max( timestamp ) from
FROM accesstable
where
USERNAME = (select distinct USERNAME from accesstable where cat_id = 2 );

====================================================
I have no clue how to make it work -:((

Could someone here help...

Thanks in advance.
/kanwal


Re: retrieve last access time for users from table [message #211479 is a reply to message #211478] Thu, 28 December 2006 21:54 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member

SELECT USERNAME , max( timestamp ) from 
FROM accesstable 
where cat_id = 2
group by username
/



Re: retrieve last access time for users from table [message #211487 is a reply to message #211479] Thu, 28 December 2006 23:20 Go to previous messageGo to next message
kanwaljitsingh
Messages: 2
Registered: December 2006
Junior Member

works absolutely fine... cheers mate -:)

One more question.

how do I retrieve category values as well and remove the "where cat_id = 2"
When I tried this, got an error message "not a GROUP BY expression"

/Kanwaljit
Re: retrieve last access time for users from table [message #211495 is a reply to message #211487] Thu, 28 December 2006 23:58 Go to previous message
bonker
Messages: 402
Registered: July 2005
Senior Member
well the error did say "not a GROUP BY expression" right
which means you have to add cat_id as part of group by clause.



SELECT USERNAME , cat_id,max( timestamp ) from 
FROM accesstable 
group by username,cat_id



Suggest you read some books/documentations on SQL .
Previous Topic: Need help in Using UTL_FILE package
Next Topic: Ignore case character wise
Goto Forum:
  


Current Time: Sat Dec 03 01:24:15 CST 2016

Total time taken to generate the page: 0.09068 seconds