Re: SQL Help

Ascendant:
select /*+ rule */ t.date, t.column2,... from Table t, dual d where t.date = decode(d.dummy (+), null,null) and date between start and end
and rownum < n+1

Descendant:
select /*+ rule */ t.date, t.column2,... from Table t, dual d where sysdate - t.date = decode(d.dummy (+), null,null) and date between start and end
and rownum < n+1

HTH
Philippe

Brent Collins wrote in message <371627A2.8BD5BBE_at_ti.com>...
>I am a novice at SQL and need some help. I have a table that has millions
of
>rows with a Date column. My users would like to select the first or last n
rows
>for a given date range. I can't figure out a good way to do this without
>returning all rows and use filtering before sending the data back to the
client.
>
>I am using Oracle 8.0.4 on Solaris 2.6. I access the data via JDBC using
JDK
>1.1.6.
>I tried using the following SQL statements.
>select * from table
> where date between start and end
> and rownum < n+1
> order by date asc/desc;
>This does not work because the ordering is done after the where clause.
>
>I also tried:
>select date, column2, column3 ... etc. from table
> where date between start and end
> and rownum < n+1
> group by date;
>This works for selecting the first n rows as long as there are no duplicate
>dates (which is not likely).
>
>Anyone know how I can accomplish this? Please copy my email address when
>replying.
>
>Any info is appreciated.
>
>--
>Brent Collins
>SC CIM System Integration
>Texas Instruments
>brent_at_ti.com
>972-997-2679
>
>
Received on Fri Apr 16 1999 - 09:08:23 CDT