Re: Getting first/last record

Careful there,

    He said he wanted to retrieve date from an ORDERED table. Since the only way to "Order" a table in Oracle is via an index the first query should specify an index. The second query should specify max on the index value rather than rowid because max rowid will return the last row added to the table.

To get the first row from a table ordered by index_col you can use:

        SELECT * FROM emp WHERE ROWNUM < 2 AND index_col > 0;

To get the last row you can use:

        SELECT * FROM emp WHERE rowid = (SELECT MAX(index_col) FROM emp WHERE index_col > 0);

If the column the table is indexed on is CHAR or VARCHAR2 compare to a space rather than to zero. If a date just compare to '01-JAN-50'

regards
Jerry Gitomer

nooruddin wrote in message <7mlr1m$kcd$1_at_news.vsnl.net.in>...
>
>To get the first record from a table -----
>
>
>select * from emp where rownum<2
>
>
>and to get the last record from the query
>
>
>select * from emp where rowid=(select max(rowid) from emp);
>
>hope this helps..
>
>bye..
>Kevin A Lewis wrote in message ...
>>does
>>
>> select to_char(sysdate,'HH24:MI:SS') from dual
>>
>>Help at all - See your "SQL Language Reference Manual" for more details
>>
>>--
>>Kevin A Lewis (BOCM PAULS LTD - Animal Feed Manufacturer - Ipswich
England)
>> <KevinALewis_at_HotMail.com>
>>
>>The views expressed herein by the author of this document
>>are not necessarily those of BOCM PAULS Ltd.
>>STEVE <spectre_at_hkstar.com> wrote in message
>>news:1duzlc6.l8llj89qdonwN@[201.1.1.73]...
>>> HI,
>>> Thanks for your Quick reply, the only thing that wories me is that the
>>> time does not actually show up, is it to second resolution?
>>> even if i do a "SELECT SYSDATE FROM DUAL;" it does not show the time.
>>> my oracle 8 bible says time is available , but does not go into it.
>>>
>>> All you book writers pay attention.
>>>
>>> steve
>>>
>>>
>>> Brian Peasland <peasland_at_email.msn.com> wrote:
>>>
>>> > >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.
>>> >
>>> >
>>> > The Oracle DATE format stores both the date and the time in one field.
>>So
>>> > you can just query the date field. Assuming the table is named
'TABLEX'
>>and
>>> > the date field is labelled 'COLX', try this:
>>> >
>>> > SELECT min(COLX), max(COLX) from TABLEX;
>>> >
>>> > Hope that helps,
>>> > Brian Peasland
>>> > peasland_at_msn.com
>>
>>
>
>
Received on Thu Jul 15 1999 - 13:14:49 CDT