Re: Syntax to return first record - Oracle ??

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/05/05
Message-ID: <8eui2n$pk1$1_at_nnrp1.deja.com>#1/1


In article <391067F7.EB5A0A8F_at_Unforgettable.com>,   Kenneth C Stahl <BlueSax_at_Unforgettable.com> wrote:
> jason wrote:
> >
> > Hi,
> > Could anyone tell me how (and if it's possible) to create a SQL
 query that
> > returns only the first (or last) record. Through Access, I could
 write :
> >
> > select top 1 changedatetime
> > from tab1
> > order by changedatetime;
> >
> > Is there an equivelant key word to 'top' (or 'bottom') when using
 standard
> > SQL ?
> > TIA
> > Jason
>
> What you are trying to do cannot be done in Oracle. Oracle is a true
> relational database - Access is not. In a true relational database it
 is
> totally absurd to think of anything as being the first row or the last
> row because that doesn't make sense in the relational world.
>

I don't know about "absurd", applications force all kinds of things on us.

In any case, in all releases of Oracle:

select min(changedatetime) from tab1;

would return the "first" changedatetime (and max would get the "last" one).

In Oracle8i, release 8.1 and up:

select *
  from ( select * from T order by .... )  where rownum = 1
/

would get you the "first" where as:

select *
  from ( select * from T order by c1 DESC, c2 DESC, ... )  where rownum = 1
/

would get the "last". So, its possible and done all of the time. In fact, Oracle8i release 8.1.6 adds a bunch of new functions in support of this (see
http://technet.oracle.com/doc/oracle8i_816/server.816/a76962/816.htm#441 29). It lets you Rank (top N type queries), get moving aggregates and other things....

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Fri May 05 2000 - 00:00:00 CEST

Original text of this message