Re: writing oracle query

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_at_yahoo.com>:
> 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.
>
>

--
http://www.freelists.org/webpage/oracle-l