Re: Converting SQL Server Top N to Oracle Equivalent

  • From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
  • To: mkb125@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 23 Oct 2008 08:50:03 +0100

That is correct, though the order by rownum is redundant as you only
have one row.  If you have a recent version of oracle then you might
also wish to check out the analytic functions as well.

On 10/23/08, mkb <mkb125@xxxxxxxxx> wrote:
> 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
>
>
>

-- 
Sent from Google Mail for mobile | mobile.google.com

Niall Litchfield
Oracle DBA
http://www.orawin.info
--
//www.freelists.org/webpage/oracle-l


Other related posts: