Re: HELP Select only 1 Record

On Thu, 24 Jun 1999 22:56:53 GMT, "Charles Tasse" <ss_at_efni.com> wrote:

>I need to find a replacement for this SQL Statement
>
>Select TOP 1 Partnumber from Part.Master order by Partnumber Desc;
>
>i need the last number returned only
>
>Oracle 8
>
>Charles
>

Select max(Partnumber) from Part.Master;

If there is an index on Partnumber Oracle will use it.

or

create or replace
FUNCTION MaxPartNumber
 RETURN Part.Master.PartNumber%Type
is
CURSOR c1 is
 select PartNumber
 from Part.Master
 order by PartNumber DESC;

C1r C1%RowType;
BEGIN open c1;
 fetch C1 into C1r;
 close C1;
 RETURN C1r.PartNumber;
END MaxPartNumber;
/

Obvious huh?

The above function can be used in SQL Queries

Any query involving restricting the rows returned from a query using rownum is not guaranteed to work due to RowNum being applied to the result set before any sorting is done.

graham Received on Sat Jul 03 1999 - 21:14:30 CEST