Hello, In the following example You will see You are right: Your query will retrieve the right set of data. create table ALG (change_id number(2), type varchar2(5), time_stamp date); create table CHG (id number(2), type varchar2(5), time_stamp date); delete from ALG; insert into ALG values(1, 'CL', sysdate); insert into ALG values(2, 'RE', sysdate+1); insert into ALG values(3, 'CL', sysdate+2); insert into ALG values(4, 'RE', sysdate+3); insert into ALG values(5, 'CL', sysdate-1); insert into ALG values(6, 'RE', sysdate-2); insert into ALG values(7, 'RE', sysdate-5); insert into ALG values(8, 'CL', sysdate+9); insert into ALG values(9, 'RE', sysdate-8); delete from CHG; insert into CHG values(1, 'CL', sysdate-6); insert into CHG values(2, 'RE', sysdate+1); insert into CHG values(3, 'CL', sysdate-8); insert into CHG values(14, 'RE', sysdate+9); insert into CHG values(15, 'CL', sysdate-4); insert into CHG values(61, 'RE', sysdate-1); insert into CHG values(71, 'RE', sysdate+2); insert into CHG values(18, 'CL', sysdate+3); insert into CHG values(91, 'RE', sysdate+11); -- I considered the existence of a type column in the CHG table -- causing small change in Your query in the references to avoid -- the ambiguity: "and (ALG.type = 'CL' or ALG.type = 'RE') " -- If You comment the restriction "where rownum <= 1" select b.time_stamp from ( select alg.time_stamp from ALG, CHG where ALG.change_id = CHG.id and (ALG.type = 'CL' or ALG.type = 'RE') order by ALG.time_stamp asc) b --where rownum <= 1 order by rownum asc / -- the result will be the following: TIME_STA -------- 08.10.23 08.10.24 08.10.25 -- If You maintain the restriction select b.time_stamp from ( select alg.time_stamp from ALG, CHG where ALG.change_id = CHG.id and (ALG.type = 'CL' or ALG.type = 'RE') order by ALG.time_stamp asc) b where rownum <= 1 order by rownum asc / -- the result will be the one You want: the first line in the set will -- make the result set TIME_STA -------- 08.10.23 drop table ALG; drop table CHG; Best regards Dias Costa > Hi, > > Gota SS2K5 query that I'm trying to convert over to Oracle 10gR2. > > This is the SS2K5 query: > > select top 1 ALG.time_stamp > from ALG, CHG > where ALG.change_id = CHG.id > and (type = 'CL' or type = 'RE') > order by ALG.time_stamp ASC > > I coded the Oracle 10gR2 equivalent as follows: > > select b.time_stamp > from ( > select alg.time_stamp > from ALG, CHG > where ALG.change_id = CHG.id > and (type = 'CL' or type = 'RE') > order by ALG.time_stamp asc) b > where rownum <= 1 > order by rownum asc > / > > Is this the correct way to do it? I wish I could check the results > between the SS2K5 db and the Oracle db but I don't have access to the SS > db and even if I did, the data sets are very different. > > Anyway, just wanted to know if I was on the right track or am I totally > off-base here. > > tia > > -- > mohammed > > > > -- > //www.freelists.org/webpage/oracle-l > > > -- //www.freelists.org/webpage/oracle-l