Sunday, April 01, 2007

TOP N doesn't exist in Oracle ??????

Hi Folks,

I have shocked when i tried to integrate with Oracle DB in one of my projects,and i found that TOP keyword doesnt exist in oracle,and you have to change it and the workaround also depends on backend DB version !!!.

if you used to write this query in sql 2000 db:

select top 5 * from Customers where customerId=110

if you want to do the same with Oracle DB (8i or later),write it like this :

select * from Customers where customerId=110 and rownum<6

rownum<6,do the same of Top 5 and return TOP 5 records

if you have Oracle DB earlier than 8i you have to write the following :

SELECT *
FROM Customers a
WHERE 5 >= (SELECT COUNT(DISTINCT Revenue)
FROM Customers b
WHERE b.Revenue>= a.Revenue)



This is Today Tip,Enjoy .Netting :)



Regards,
Moustafa arafa
Post a Comment