Re: SQL problem

Further to you later posts, there are some versions of 7.3 which allow ORDER BY in in-line views; I think it appeared by accident in 7.3.4.4.

However, assuming you have an index on the TIME column, the following form of SQL might work:

select * from
(

    select

        *
    from log_table
    where time_col >= sysdate - 1/24
    union
    select /*+ index_desc(log_table, idx_time) */

        *
    from log_table
    where rownum <= 30
)
order by

    time_col desc;

The top half gets you all the rows in the last hour. The bottom half uses the index_desc hint to traverse the index on time in descending order and gets you the latest 30 rows (personally I don't really like using index_desc in this way).
The UNION eliminates duplicates.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html






Rok Kodrun wrote in message <9hfra1$cme$1_at_goodnews.ijs.si>...


>I have a LOG table with LOG_ID, TIME and TEKST columns

>I want to get records for the last hour (or certain time) (x records) and

 if


>less then N records in last hour, then get also last N-x records so that I

>get N records alltogether. And I wnat them ordered descening (last record

>first)

>

>I tried with embedded select with ROWNUM and COUNT and so on but failed to

>get right records, because you can not order in embeded SELECT.

>

>Please help

>

>Rok

>

>

>