| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Getting first/last record
mflahertyLUQTJC_at_ukonline.co.uk wrote:
>
> And as MAX(col_name) performs a full table scan does anyone have any
> ideas on using any Relational Databases, such as Oracle, to obtain
> the last value stored ?
>
> This is something we are looking at here. we don't want to do a
> full table scan every time as our tables are very large, ie 4Gb +
> for a table will be the norm.
>
> Our current solution incorporates our own 'catalogue' tables which
> track the latest generation times of records in the table. This can
> then be used to pick out, using an index on generation_time, the
> latest record from our data table. The generation_time in our
> catalogue is filled using an after update trigger.
>
> Mike.
>
> >In a relational database there is no concept of first and last
> record. For
> >any given query there will be, of course, the first record returned
> and the
> >final record returned, but it is entirely based on the query not on
> the
> >table. In addition, there is also no concept of an ordered table. It
> is
> >possible to load a table from data that is sorted and in many cases
> if you
> >perform a simple query (i.e. select * from table1;) you will get it
> back in
> >the same order, but to some degree that is coincidence.
> >
> >Ken
> >
> >STEVE wrote:
> >
> >> Hi,
> >> Being a bit of a newbee, to oracle8 can anybody help with the
> following.
> >> 1.How to get the first record in an ordered table
> >> 2 How to get the last record in an ordered table.
> >> the order being on date then time.
> >>
> >> In fox pro it's easy,
> >> set order to date88 (index)
> >> go top
> >> go bottom
> >>
> >> steve
> >
>
> Please remove the LUQTJC before replying by email.
>
> ***** Posted via the UK Online online newsreader *****
>
> Go to http://www.ukonline.co.uk to find out
> about other online services we offer our subscribers.
If your column is indexed, then you can use
select /*+ INDEX_DESC(t index_name) */ my_column
from my_table t
where rownum < 2
which scoots down to the "bottom" of the index and grabs the max value...
HTH
--
"Some days you're the pigeon, and some days you're the statue." Received on Tue Jul 20 1999 - 08:56:54 CDT
 |
 |