| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> writing oracle query
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
 |
 |