writing oracle query
- From: laura pena <lizzpenaorclgrp@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 7 Feb 2006 09:17:37 -0800 (PST)
I have the following query that gets information from a recorderid that is the
last ones entered :
select *
from stateofhealth soh, (
select type, typeidentifier, max(lastmodified)
lastmodified
from stateofhealth where recorderId=14 and
type='ATA-BACKLOGS' or type = 'ATA-CALLCOUNTS'
group by type, typeidentifier
) b
where soh.type = b.type
and soh.typeidentifier = b.typeidentifier
and soh.lastmodified = b.lastmodified
Wondering if there is a way to re-write without using select statement in from
clause.
I guess I could create a view and join with a view.
Here is some information on this table.
It's a ranged partitioned table by lastmodified.
Primary Key is stateofhealthid.
I want all information from a recorderid that is the last entered (ie.
lastmodified desc). type, typeidentifier, max(lastmodified) gives me this
information I just need the rest of the columns.
SQL> desc stateofhealth;
Name Null? Type
----------------------------------------- -------- ------------------
STATEOFHEALTHID NOT NULL NUMBER(10) Primary Key
RECORDERID NOT NULL NUMBER(10)
TYPEIDENTIFIER NOT NULL NUMBER(5)
LASTMODIFIED NOT NULL DATE partitioned by month
TYPE NOT NULL VARCHAR2(30)
STATUS VARCHAR2(5)
MESSAGE VARCHAR2(256)
Just wondering if there was any other way to re-write.
As always your comments/questions are appreciated.
Many Thanks,
-Lizz
---------------------------------
Brings words and photos together (easily) with
PhotoMail - it's free and works with Yahoo! Mail.
Other related posts:
