Re: writing oracle query
- From: Gints Plivna <gints.plivna@xxxxxxxxx>
- To: lizzpenaorclgrp@xxxxxxxxx
- Date: Tue, 7 Feb 2006 20:10:17 +0200
Assuming you are at least on 9i you can use something like
SELECT * FROM (
SELECT soh.*,
row_number() OVER (PARTITION BY type, typeidentifier ORDER BY
lastmodified desc) rn
FROM stateofhealth soh
WHERE recorderId=14
AND type='ATA-BACKLOGS'
OR type = 'ATA-CALLCOUNTS'
)
WHERE rn = 1
And check one more time do you really need
WHERE recorderId=14
AND type='ATA-BACKLOGS'
OR type = 'ATA-CALLCOUNTS'
or probably (look at parenthesis)
WHERE recorderId=14
AND (type='ATA-BACKLOGS'
OR type = 'ATA-CALLCOUNTS' )
Gints Plivna
2006/2/7, laura pena <lizzpenaorclgrp@xxxxxxxxx>:
> 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.
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
