Re: oracle rownum

"odelya" <be.special_at_gmail.com> schreef in bericht news:1187707946.000103.182750_at_a39g2000hsc.googlegroups.com...
>
>
> Tha sort is not by the ID - it's by another parameter in the table
> (date..)
>

Then your query should

1 - select the right record by id, and it's date

2 - select the record from your table with maximum date where date < date of first record

Something like
select *
from <your table> t1
where date_column =

   (
    select max(date_column)
    from <your table> t2
    and t2.date_column < date_column_of_found_record_in_first_query    )

• I think the double select from the same table could be avoided by a group by , order by and rownum=1

3 - select the record from your table with minimum date where date > date of first record

• Change max for min and < for >

assuming all dates are different or in case they are the same you don't care which record is there. (< and > should then be <= and => ).

You could do this with a union of these queries, a standard, min and max query.

But maybe there are some functions for this....

One different trick to rule out one of the union tables is to find your record and join it with a select over your table order by date of first record - date of queried record and find the smallest negative and positive differences.... but that would be too complex I guess..

OR: look on internet and search for Nearest Neighbor Algorithms

OR (Last option):
wait for experts like Michel Cadot to answer your question. He's an expert in queries like these (and others!) I'm sure there must be more efficicient options for this problem.

Shakespeare Received on Tue Aug 21 2007 - 10:27:55 CDT