CREATE TABLE [dbo].[LOG]
(
[TYPE] [smallint] NULL ,
[TIME_STAMP] [datetime],
[ID] [varchar] (44)
)
ID is non-unique. I want to select all IDs where the last entry for
that ID is of type 11.
Below is the query that I used. Notice that the subquery used is a
nested (not correlated) subquery meaning that it doesn't use results
of outer query. This subquery should only be executed once. However,
on large number of rows (3 million), this query never returns.
I have also attempted to run subquery separately. That takes 1 minute.
Then I put the results in temp table and joined that temp table with
the main query. That takes about 2 minutes.
Unfortunately, that solution is unacceptable to us since we have to
support both MSSQL and Oracle with the same queries, and the syntax
for temp tables or table variables is different in Oracle.
Mysterious.
Here's the query:
-- main query
select ID
from log
where ID in
(
-- subquery
select id from log l1
where time_stamp =
(
select max(time_stamp)
from log l2
where l2.id = l1.id
and l2.type = 11
)
)