- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Pull latest entry in table
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-12-2006 02:48 AM
тАО05-12-2006 02:48 AM
SELECT empid
FROM v_emp
WHERE empid NOT IN (SELECT empid
FROM v_emphist
WHERE logon_time > SYSDATE - 90)
EMPID
----------
BRIDER
DFLINSTONE
DSMITH
JREEVES
4 rows selected
What I would like to do is also pull the last login time along with the empid.
But...
v_emphist holds all login times.
I have two tables one holds the empid and other data, the other table holds empid and login times.
But the data looks like this:
emp_hist table
EMPID LOGON_TIME
---------- --------------------
ABACUS 5/5/2006 8:04:44 AM
ABACUS 5/4/2006 7:00:33 AM
ABACUS 5/3/2006 7:32:37 AM
ABACUS 5/2/2006 7:01:51 AM
ABACUS 5/1/2006 8:11:48 AM
ABOLES 4/26/2006 7:15:14 AM
ABOLES 4/25/2006 10:05:43 AM
ABOLES 4/25/2006 8:40:18 AM
ABOLES 4/25/2006 8:06:53 AM
And so on for all id's
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-12-2006 02:59 AM
тАО05-12-2006 02:59 AM
Re: Pull latest entry in table
SELECT empid, max(b.logon_time)
FROM v_emp a, v_emphist b
WHERE a.empid NOT IN (SELECT empid
FROM v_emphist
WHERE logon_time > SYSDATE - 90)
and a.empid = b.emp_id
Patti
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-12-2006 06:04 AM
тАО05-12-2006 06:04 AM
SolutionTry this instead, it should run substantially faster.
SELECT empid, max(b.logon_time)
FROM v_emp a, v_emphist b
WHERE 0 =
(
SELECT count(*)
FROM v_emphist c
WHERE .empid = a.empid
AND logon_time > SYSDATE - 90
)
and a.empid = b.emp_id;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-12-2006 06:27 AM
тАО05-12-2006 06:27 AM
Re: Pull latest entry in table
SELECT empid, max(logon_time)
FROM v_emphist
WHERE 0 =
(
SELECT count(*)
FROM v_emphist
WHERE logon_time > SYSDATE - 90
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-12-2006 06:53 AM
тАО05-12-2006 06:53 AM
Re: Pull latest entry in table
SELECT empid, max(logon_time)
FROM v_emphist
WHERE 0 =
(
SELECT count(*)
FROM v_emphist
WHERE logon_time > SYSDATE - 90
)
group by empid;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-12-2006 06:56 AM
тАО05-12-2006 06:56 AM
Re: Pull latest entry in table
Try the following:
SELECT empid, max(logon_time)
FROM v_emphist a
WHERE 0 =
(
SELECT count(*)
FROM v_emphist b
WHERE b.empid=a.empid
AND logon_time > SYSDATE - 90
)
GROUP BY empid;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-15-2006 10:16 AM
тАО05-15-2006 10:16 AM
Re: Pull latest entry in table
But if I run this, I get 171 records returned.
SELECT empid
FROM v_emp
WHERE empid NOT IN (SELECT empid
FROM v_emphist
WHERE logon_time > SYSDATE - 90)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-23-2006 01:39 PM
тАО11-23-2006 01:39 PM