Oracle Top n

[Brad]

Does anyone know the equivalent Oracle SQL command for the Access command
SELECT TOP 1 FROM Employees ??

Thank you in advance.

Brad

[Craig Clearman]

Brad,

>Does anyone know the equivalent Oracle SQL command for the Access command
>SELECT TOP 1 FROM Employees ??

SELECT * FROM employees WHERE ROWNUM <= 1

Ciao, Craig

[venkatesh]

You can use same syntax SELECT TOP 1 FROM Employees in SQL Server. I think
you need to be atleast in SQL Server 7.0 to make it work.
The alternative is to set the rowcount before running the query as shown:
SET ROWCOUNT 4
GO


"Brad" <BJHugh@aol.com> wrote:
>
>Does anyone know the equivalent Oracle SQL command for the Access command
>SELECT TOP 1 FROM Employees ??
>
>Thank you in advance.
>
>Brad

[ravi]

in oracle records are stored in haphazard manner..i mean not the way they
were inserted...
so what exactly you want to retreive...
do u want the record with highest salary or likewise...
it depends...

select * from employee where rownum<=1
will retreive only 1 record but it may be any record in that table...

"Brad" <BJHugh@aol.com> wrote:
>
>Does anyone know the equivalent Oracle SQL command for the Access command
>SELECT TOP 1 FROM Employees ??
>
>Thank you in advance.
>
>Brad

[fritz]

In the absence of an ORDER BY clause in your SQL statements, oracle returns
the rows based on how they are stored, which is usually related to to how
the data was inserted into the table (unless it was exported then imported
again during a reorg). IF you want your records to be stored in a specified
order, look into the INDEX ORGANIZED table option.


"ravi" <ravindra.babupochampally@gecapital.com> wrote:
>
>in oracle records are stored in haphazard manner..i mean not the way they
>were inserted...
>so what exactly you want to retreive...
>do u want the record with highest salary or likewise...
>it depends...
>
>select * from employee where rownum<=1
>will retreive only 1 record but it may be any record in that table...
>
>"Brad" <BJHugh@aol.com> wrote:
>>
>>Does anyone know the equivalent Oracle SQL command for the Access command
>>SELECT TOP 1 FROM Employees ??
>>
>>Thank you in advance.
>>
>>Brad
>

[Greg Nash]

True. I do have one use for a "TOP 1" without an ORDER -- if I'm working with a
command prompt and can't remember the names of the fields in a table. Just
select * but return only 1 row - you get a row of sample data as a bonus!

(Actually, I'm using DB2, where the syntax is
SELECT * FROM mytable FETCH FIRST ROW ONLY
or, say, FETCH FIRST 5 ROWS ONLY
--Greg

fritz wrote:

> In the absence of an ORDER BY clause in your SQL statements, oracle returns
> the rows based on how they are stored, which is usually related to to how
> the data was inserted into the table (unless it was exported then imported
> again during a reorg). IF you want your records to be stored in a specified
> order, look into the INDEX ORGANIZED table option.
>
> "ravi" <ravindra.babupochampally@gecapital.com> wrote:
> >
> >in oracle records are stored in haphazard manner..i mean not the way they
> >were inserted...
> >so what exactly you want to retreive...
> >do u want the record with highest salary or likewise...
> >it depends...
> >
> >select * from employee where rownum<=1
> >will retreive only 1 record but it may be any record in that table...
> >
> >"Brad" <BJHugh@aol.com> wrote:
> >>
> >>Does anyone know the equivalent Oracle SQL command for the Access command
> >>SELECT TOP 1 FROM Employees ??
> >>
> >>Thank you in advance.
> >>
> >>Brad
> >