Re: Syntax to return first record - Oracle ??
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