RE: add a column in oracle

[Jason.Gulledge]

Here's a bit of a detailed explanation of Dave means.   Something to note about using "where rownum < 4" in Oracle is that it doesn't pull back the "TOP" type records.  It'll simply pull back the first 3 rows that exist first in the database, and not any specialized order you want.


However, there is a fairly simple workaround.  Using a subquery will give you the "TOP N" effect that MySQL and others.  It works like this:


Say you wanted to pull back 10 of the highest paid persons at your company.  Here's the syntax.


--This pulls back the highest ones first, in a "top" type order.
select name, salary from
(
    ---this does your ordering for you
    select name,  salary from employee_data
    order by salary desc
)
where rownum < 11


Hope that helps clear it up,


Jason Gulledge
ALLTEL

-----Original Message-----
From: Dave Carabetta [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 05, 2004 2:29 PM
To: CF-Talk
Subject: Re: add a column in oracle

>Sorry, wrong database...
>
>SELECT * FROM foo WHERE rownum < 4
>

Note that this will not work if you have any sort of ORDER BY clause because
Oracle uses the rownum clause before the ORDER BY. This is why I posted the
other way of doing SQL Server's version of TOP.

Regards,
Dave.
  _____

---

[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

---