Re: Converting SQL Server Top N to Oracle Equivalent

From: <dcosta_at_lnec.pt>
Date: Thu, 23 Oct 2008 10:12:41 +0100 (WEST)
Message-ID: <1743.83.132.236.53.1224753161.squirrel@lepus.lnec.pt>

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
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Thu Oct 23 2008 - 04:12:41 CDT