Inner Join Performance This is my first question on this forum, so be patient...
My question is about the most efficient way to build a query on the following table:

create table OpenTrades(
    AccountNumber   number,
    SnapshotTime    date,
    Ticket      number,
    OpenTime    date,
    TradeType   varchar2(4),
    TradeSize   number,
    TradeItem   char(6),
    OpenPrice   number,
    TradeSL     number,
    TradeTP     number,
    TradeSwap   number,
    TradeProfit number
);
alter table OpenTrades add constraint OpenTrades_PK Primary Key (AccountNumber, SnapshotTime, Ticket) using index tablespace MyNNIdx;

This table is populated every 15 minutes, and the timestamp of the insert is saved in SnapshotTime column.
The sought query should group records by Week number (based on SnapshotTime), and filter the records so that it would return those with the latest (SnapshotTime) within the same week. 

So far, I've tried the following:

select max(ot2.SnapshotTime) from OpenTrades ot1, OpenTrades ot2 where to_date(ot1.SnapshotTime,'IW')=to_date(ot2.SnapshotTime,'IW');.


However, this takes way longer than expected (15 minutes for less than 1500 rows); I'm sure there's a much better way to write this, but so far, it has eluded me. Any idea?
Thanks!
Try something like:

select AccountNumber, SnapshotTime, Ticket, ...
from (
    select AccountNumber, SnapshotTime, Ticket, ...
         , row_number() over (partition by AccountNumber, Ticket
                              order by SnapshotTime desc) as rn
    from OpenTrades
) T
where rn = 1;


I just reread the post and realized that I gave a solution for something different than the OP asked for, I guess something like:

select AccountNumber, SnapshotTime, Ticket, ...
from (
    select AccountNumber, SnapshotTime, Ticket, ...
         , row_number() over (to_date(SnapshotTime,'IW')
                              order by SnapshotTime desc) as rn
    from OpenTrades
) T
where rn = 1;


is closer to what the OP wants.